package com.ugoodtech.mdcc.web.controller;

import com.mysema.query.BooleanBuilder;
import com.ugoodtech.mdcc.core.Constants;
import com.ugoodtech.mdcc.core.LogConstants;
import com.ugoodtech.mdcc.core.domain.Department;
import com.ugoodtech.mdcc.core.domain.KpiPaintReportVO.ServiceResponseVo;
import com.ugoodtech.mdcc.core.domain.ModuleAuthority;
import com.ugoodtech.mdcc.core.domain.QUserProfile;
import com.ugoodtech.mdcc.core.domain.QuestionAnswerOption;
import com.ugoodtech.mdcc.core.domain.User;
import com.ugoodtech.mdcc.core.domain.UserProfile;
import com.ugoodtech.mdcc.core.domain.UserType;
import com.ugoodtech.mdcc.core.domain.VstarCustomerSatisfactionReport.VstarCustomerSatisfactionReport;
import com.ugoodtech.mdcc.core.domain.VstarServiceReport.VstarServiceReport;
import com.ugoodtech.mdcc.core.dto.ServiceStatChartListResponse;
import com.ugoodtech.mdcc.core.dto.ServiceStatChartResponse;
import com.ugoodtech.mdcc.core.repository.DepartmentRepository;
import com.ugoodtech.mdcc.core.repository.ModuleAuthorityRepository;
import com.ugoodtech.mdcc.core.repository.QuestionAnswerOptionRepository;
import com.ugoodtech.mdcc.core.repository.UserProfileRepository;
import com.ugoodtech.mdcc.core.repository.UserRepository;
import com.ugoodtech.mdcc.core.service.VstarCoreServiceReportService;
import com.ugoodtech.mdcc.core.utils.DateUtil;
import com.ugoodtech.mdcc.core.utils.WordUtil;
import com.ugoodtech.mdcc.web.dto.JsonResponse;
import com.ugoodtech.mdcc.web.dto.KpiResponseForOut;
import com.ugoodtech.mdcc.web.dto.LabelValue;
import com.ugoodtech.mdcc.web.dto.SearchParamDTO;
import com.ugoodtech.mdcc.web.pojo.dto.AddCustomReportDTO;
import com.ugoodtech.mdcc.web.service.ExportReportService;
import com.ugoodtech.mdcc.web.utils.SvgRasterizer;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.UnsupportedEncodingException;
import java.text.DecimalFormat;
import java.util.*;

@Api(description = "常规服务报告统计")
@RestController
@RequestMapping("/VstarServiceReport")
public class VstarServiceReportController extends BaseController {

    @Autowired
    private VstarCoreServiceReportService vstarCoreServiceReportService;

    @Autowired
    private UserRepository userRepository;

    @Autowired
    private ModuleAuthorityRepository moduleAuthorityRepository;

    @Autowired
    private ExportReportService exportReportService;

    @Autowired
    private WeekServiceReportServiceController weekServiceReportServiceController;

    @Autowired
    private UserProfileRepository userProfileRepository;

    @Autowired
    private QuestionAnswerOptionRepository questionAnswerOptionRepository;

    @Autowired
    private com.ugoodtech.mdcc.web.service.VstarServiceReportService serviceReportService;

    @Autowired
    private DepartmentRepository departmentRepository;

    private static final Logger logger = LoggerFactory.getLogger(VstarServiceReportController.class);

    @ApiOperation("统计常规服务报告")
    @RequestMapping(value = "serviceReportStat", method = RequestMethod.GET)
    public JsonResponse serviceReportStat(@ApiParam("登陆用户ID") @RequestParam(value = "loginUserId") Long loginUserId,
                                          @ApiParam("省份多选") @RequestParam(value = "provinces", required = false) String provinces,
                                          @ApiParam("品牌") @RequestParam(value = "brandname", required = false) String brandname,
                                          @ApiParam("维修站类型") @RequestParam(value = "category", required = false) String category,
                                          @ApiParam("维修站名称") @RequestParam(value = "stationName", required = false) String name,
                                          @ApiParam("服务开始时间") @RequestParam(value = "serviceStartTime", required = false) Long serviceStartTime,
                                          @ApiParam("服务结束时间") @RequestParam(value = "serviceEndTime", required = false) Long serviceEndTime,
                                          @ApiParam("开始时间") @RequestParam(value = "startTime", required = false) Long startTime,
                                          @ApiParam("结束时间") @RequestParam(value = "endTime", required = false) Long endTime,
                                          @ApiParam("服务项目") @RequestParam(value = "serviceType", required = false) Long serviceType,
//                                          @ApiParam("服务次数") @RequestParam(value = "serviceNum", required = false) Integer serviceNum,
                                          @ApiParam("技术人员姓名") @RequestParam(value = "userName", required = false) String userName,
//                                          @ApiParam("是否有待跟进") @RequestParam(value = "isFollowUp", required = false) String isFollowUp,
//                                          @ApiParam("处理状态") @RequestParam(value = "followType", required = false) String followType,
//                                          @ApiParam("人员类型") @RequestParam(value = "userType", required = false) String userType,
                                          @ApiParam("是否填写满意度报告") @RequestParam(value = "isCustomer", required = false) Boolean isCustomer,
                                          @ApiParam("客户状态ID") @RequestParam(value = "customerStat", required = false) Long customerStat,
                                          @ApiParam("客户类型ID") @RequestParam(value = "customerType", required = false) Long customerType,
                                          @ApiParam("油漆品牌ID") @RequestParam(value = "paintBrand", required = false) String patintBrand,
                                          @ApiParam("品牌定位ID") @RequestParam(value = "productPosition", required = false) String productPosition,
                                          @ApiParam(value = "维修站编号") @RequestParam(value = "stationNum", required = false) String stationNum,
                                          @ApiParam(value = "部门ID") @RequestParam(value = "departmentId", required = false) Long departmentId) {
        isUserLogin();
        User user = userRepository.findOne(loginUserId);
        if (user == null) {
            return JsonResponse.errorResponseWithError("抱歉", "未找到当前登陆用户");
        }
//        ModuleAuthority moduleAuthority = moduleAuthorityRepository.findByName(Constants.REPORT_STATISTICS_PC);
//        boolean tf = user.getAuthorities().contains(moduleAuthority);
//        if (tf == false) {
//            return JsonResponse.errorResponseWithError("抱歉", "没有权限查看报告统计（PC）");
//        }
        VstarServiceReport vstarServiceReport = vstarCoreServiceReportService.statReport(provinces, brandname, category, name,
                serviceStartTime, serviceEndTime, startTime, endTime, serviceType, null,
                userName, "", "", user.getId(), "", isCustomer,
                customerStat, customerType, patintBrand, productPosition, stationNum, departmentId);
        return JsonResponse.successResponseWithData(vstarServiceReport);
    }

    @ApiOperation("统计常规服务报告--筛选条件为 维修站 名称 品牌")
    @RequestMapping(value = "getServiceChart", method = RequestMethod.GET)
    public com.ugoodtech.mdcc.core.dto.JsonResponse getServiceChart(@ApiParam("登陆用户ID") @RequestParam(value = "loginUserId") Long loginUserId,
                                                                    @ApiParam("省份多选") @RequestParam(value = "provinces", required = false) String provinces,
                                                                    @ApiParam("品牌") @RequestParam(value = "brandname", required = false) String brandname,
                                                                    @ApiParam("维修站名称") @RequestParam(value = "stationName", required = false) String stationName,
                                                                    @ApiParam("技术人员姓名") @RequestParam(value = "userName", required = false) String userName,
                                                                    @ApiParam("部门") @RequestParam(value = "departmentId", required = false) String departmentId,
                                                                    @ApiParam("开始时间") @RequestParam(value = "startTime", required = false) Long startTime,
                                                                    @ApiParam("结束时间") @RequestParam(value = "endTime", required = false) Long endTime,
                                                                    @ApiParam("服务开始时间") @RequestParam(value = "serviceStartTime", required = false) Long serviceStartTime,
                                                                    @ApiParam("服务结束时间") @RequestParam(value = "serviceEndTime", required = false) Long serviceEndTime,
                                                                    @ApiParam("维修站类型") @RequestParam(value = "category", required = false) String category) {
//        isUserLogin();
        User user = userRepository.findOne(loginUserId);
        if (user == null) {
            return com.ugoodtech.mdcc.core.dto.JsonResponse.errorResponseWithError("抱歉", "未找到当前登陆用户");
        }
        return serviceReportService.getServiceChart(user, provinces, brandname, stationName, userName, departmentId, startTime, endTime, serviceStartTime, serviceEndTime, category);
    }

    @ApiOperation("统计常规服务报告--筛选条件为 维修站 名称 品牌")
    @RequestMapping(value = "getServiceChart", method = RequestMethod.POST)
    public com.ugoodtech.mdcc.core.dto.JsonResponse getServiceChartOut(HttpServletRequest request,
                                                                       @RequestBody KpiResponseForOut kpiResponse,
                                                                       @ApiParam("登陆用户ID") @RequestParam(value = "loginUserId") Long loginUserId,
                                                                       @ApiParam("省份多选") @RequestParam(value = "provinces", required = false) String provinces,
                                                                       @ApiParam("品牌") @RequestParam(value = "brandname", required = false) String brandname,
                                                                       @ApiParam("维修站名称") @RequestParam(value = "stationName", required = false) String stationName,
                                                                       @ApiParam("技术人员姓名") @RequestParam(value = "userName", required = false) String userName,
                                                                       @ApiParam("部门") @RequestParam(value = "departmentId", required = false) String departmentIds,
                                                                       @ApiParam("开始时间") @RequestParam(value = "startTime", required = false) Long startTime,
                                                                       @ApiParam("结束时间") @RequestParam(value = "endTime", required = false) Long endTime,
                                                                       @ApiParam("服务开始时间") @RequestParam(value = "serviceStartTime", required = false) Long serviceStartTime,
                                                                       @ApiParam("服务结束时间") @RequestParam(value = "serviceEndTime", required = false) Long serviceEndTime,
                                                                       @ApiParam("维修站类型") @RequestParam(value = "category", required = false) String category) {
        isUserLogin();
        User user = userRepository.findOne(loginUserId);
        if (user == null) {
            return com.ugoodtech.mdcc.core.dto.JsonResponse.errorResponseWithError("抱歉", "未找到当前登陆用户");
        }
        com.ugoodtech.mdcc.core.dto.JsonResponse jsonResponse =
                getServiceChart(loginUserId, provinces, brandname, stationName, userName, departmentIds, startTime, endTime, serviceStartTime, serviceEndTime, category);
        ServiceStatChartListResponse response = (ServiceStatChartListResponse) jsonResponse.getData();
        List<String> kpiRequest = kpiResponse.getKpiResponse();
        String templetPath = request.getSession().getServletContext().getRealPath("");
        String fileName = "漆星服务运营分析.xlsx";
        //sheet名
        String sheetName = "漆星服务运营分析";
        if (response.getType().equals("station")) {
            sheetName = "漆星服务运营分析-维修站";
        } else if (response.getType().equals("brand")) {
            sheetName = "漆星服务运营分析-品牌";
        } else if (response.getType().equals("user")) {
            sheetName = "漆星服务运营分析-技术人员";
        }
        XSSFWorkbook wb = getServiceChartExcel(sheetName, null, response, kpiRequest, response.getType());
        //响应到客户端
        try {
            FileOutputStream outputStream = new FileOutputStream(new File(templetPath + "/export/" + fileName));
            wb.write(outputStream);
            outputStream.close();
            //导出日志============================================
            try {
                Map<String, Object> searchParams = new HashMap<>();
                searchParams.put("省份", provinces);
                searchParams.put("品牌", brandname);
                searchParams.put("维修站名称", stationName);
                searchParams.put("技术人员姓名", userName);
                if (!StringUtils.isEmpty(departmentIds)) {
                    String[] departmentArray = departmentIds.split(",");
                    List<String> departments = new ArrayList<>();
                    for (String department : departmentArray) {
                        Department departmentObj = departmentRepository.findOne(Long.valueOf(department));
                        if (null != departmentObj) {
                            departments.add(departmentObj.getName());
                        }
                    }
                    searchParams.put("部门", StringUtils.join(departments, ","));
                }
                searchParams.put("技术人员姓名", userName);
                searchParams.put("维修站类型", category);

                if (null != serviceStartTime) {
                    searchParams.put("服务开始时间", com.ugoodtech.mdcc.core.utils.DateUtil.formatDate(new Date(serviceStartTime), Constants.DATE_FORMAT));
                }
                if (null != serviceEndTime) {
                    searchParams.put("服务结束时间", com.ugoodtech.mdcc.core.utils.DateUtil.formatDate(new Date(serviceEndTime), Constants.DATE_FORMAT));
                }
                if (null != startTime) {
                    searchParams.put("开始时间", com.ugoodtech.mdcc.core.utils.DateUtil.formatDate(new Date(startTime), Constants.DATE_FORMAT));
                }
                if (null != endTime) {
                    searchParams.put("结束时间", DateUtil.formatDate(new Date(endTime), Constants.DATE_FORMAT));
                }
                String desc = "筛选了:";
                for (String key : searchParams.keySet()) {
                    if (null != searchParams.get(key) && !StringUtils.isEmpty(searchParams.get(key).toString())) {
                        desc += (key + "=>" + searchParams.get(key) + " ,");
                    }
                }
                if ("筛选了:".equals(desc)) {
                    desc = "";
                }
                Map<String, Object> logData = new HashMap<>();
                logData.put("str", desc);
                userLogService.addUserLog(this.getCurrentUser(), null,
                        LogConstants.operate_type_send_export,
                        LogConstants.business_type_regular_report_statistics2, null, logData);
            } catch (Exception e) {
                e.printStackTrace();
            }
            String filePath = Constants.v_starServiceUrl + "/export/" + fileName;
//            String filePath = "http://vr.duluxdesigner.com.cn/export/" + fileName;
//            String filePath = "http://116.62.64.140:51002/export/" + fileName;
//            filePath = "http://116.62.64.140:51002" + filePath;

            return com.ugoodtech.mdcc.core.dto.JsonResponse.successResponseWithData(filePath);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return com.ugoodtech.mdcc.core.dto.JsonResponse.successResponse();
    }

    private XSSFWorkbook getServiceChartExcel(String sheetName, XSSFWorkbook workbook, ServiceStatChartListResponse responsList, List<String> kpiRequest, String type) {
        // 第一步，创建一个XSSFWorkbook，对应一个Excel文件
        if (workbook == null) {
            workbook = new XSSFWorkbook();
        }
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = workbook.createSheet(sheetName);
        XSSFDataFormat xssfDataFormat = workbook.createDataFormat();
        for (int i = 0; i < 19; i++) {
            if (i == 6) {
                continue;
            }
            sheet.setColumnWidth(i, 15 * 2 * 256);
        }
        XSSFDrawing patriarch = sheet.createDrawingPatriarch();
        XSSFFont typeFont = workbook.createFont();
        typeFont.setFontName("微软雅黑");
        typeFont.setFontHeightInPoints((short) 16);
        typeFont.setBold(true);
        XSSFFont titleFont = workbook.createFont();
        titleFont.setFontName("微软雅黑");
        titleFont.setFontHeightInPoints((short) 10);
        titleFont.setBold(true);
        XSSFFont bodyFont = workbook.createFont();
        bodyFont.setFontName("微软雅黑");
        bodyFont.setFontHeightInPoints((short) 10);
        String titleType = "";
        if (type.equals("station")) {
            titleType = "维修站名称：";
        } else if (type.equals("brand")) {
            titleType = "品牌：";
        } else if (type.equals("user")) {
            titleType = "技术人员：";
        }

        XSSFCellStyle typeStyle = workbook.createCellStyle();
        typeStyle.setAlignment(HorizontalAlignment.CENTER);
        typeStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        typeStyle.setFont(typeFont);

        XSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        XSSFColor titleColor = new XSSFColor(new java.awt.Color(68, 114, 196));
        titleStyle.setFillForegroundColor(titleColor);
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle.setBorderLeft(BorderStyle.THIN);
        titleStyle.setLeftBorderColor(titleColor);
        titleStyle.setBorderRight(BorderStyle.THIN);
        titleStyle.setRightBorderColor(titleColor);
        titleStyle.setBorderTop(BorderStyle.THIN);
        titleStyle.setTopBorderColor(titleColor);
        titleStyle.setFont(titleFont);

        XSSFCellStyle bodyStyle = workbook.createCellStyle();
        bodyStyle.setAlignment(HorizontalAlignment.CENTER);
        bodyStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        XSSFColor bodyColor = new XSSFColor(new java.awt.Color(180, 198, 231));
        bodyStyle.setFillForegroundColor(bodyColor);
        bodyStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        bodyStyle.setBorderLeft(BorderStyle.THIN);
        bodyStyle.setLeftBorderColor(bodyColor);
        bodyStyle.setBorderRight(BorderStyle.THIN);
        bodyStyle.setRightBorderColor(bodyColor);
        bodyStyle.setBorderTop(BorderStyle.THIN);
        bodyStyle.setTopBorderColor(bodyColor);
        bodyStyle.setFont(bodyFont);

        XSSFCellStyle bodyStyle2 = workbook.createCellStyle();
        bodyStyle2.setAlignment(HorizontalAlignment.CENTER);
        bodyStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
        XSSFColor bodyColor2 = new XSSFColor(new java.awt.Color(217, 225, 242));
        bodyStyle2.setFillForegroundColor(bodyColor2);
        bodyStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        bodyStyle2.setBorderLeft(BorderStyle.THIN);
        bodyStyle2.setLeftBorderColor(bodyColor2);
        bodyStyle2.setBorderRight(BorderStyle.THIN);
        bodyStyle2.setRightBorderColor(bodyColor2);
        bodyStyle2.setBorderTop(BorderStyle.THIN);
        bodyStyle2.setTopBorderColor(bodyColor2);
        bodyStyle2.setFont(bodyFont);


        String[] title_user = {"维修站名称", "服务次数", "服务天数", "服务项目", "满意度打分调研次数"};
        String[] title_station = {"人名", "服务次数", "服务天数", "服务项目", "满意度打分调研次数"};
        String[] title_brand = {"维修站名称", "服务人数", "服务次数", "服务天数", "服务项目", "满意度打分调研次数"};
        Integer rowNum = 0;
        Integer titleRowNum = 1;
        try {
            int index = 0;
            for (ServiceStatChartResponse response : responsList.getChartResponseList()) {
                //数据
                List<String[]> strList = response.getStrList();
                if (strList == null || strList.size() <= 0) {
                    continue;
                }
                XSSFRow rowIndex0 = sheet.createRow(rowNum);
                XSSFCell title = rowIndex0.createCell(2);
                title.setCellStyle(typeStyle);
                title.setCellValue(titleType + response.getName());
                XSSFRow titleRow = sheet.createRow(titleRowNum);
                //判断选择哪种标题
                String[] titleList = null;
                if (type.equals("station")) {
                    titleList = title_station;
                } else if (type.equals("user")) {
                    titleList = title_user;
                } else if (type.equals("brand")) {
                    titleList = title_brand;
                }
                //创建标题
                XSSFCell cell = null;
                for (int i = 0; i < titleList.length; i++) {
                    cell = titleRow.createCell(i);
                    cell.setCellValue(titleList[i]);
                    cell.setCellStyle(titleStyle);
                }

                //加入合计数组
                strList.add(response.getCountArray());

                XSSFCell cell2 = null;
                for (int i = 0; i < strList.size(); i++) {
                    titleRow = sheet.createRow(i + titleRowNum + 1);
                    for (int j = 0; j < strList.get(i).length; j++) {
                        cell2 = titleRow.createCell(j);
//                        cell2.setCellStyle(bodyStyle);
                        //将内容按顺序赋给对应的列对象
//                        cell2.setCellValue(strList.get(i)[j]);
                        if (j == strList.get(i).length - 1) {
                            if (i % 2 == 0) {
                                cell2.setCellStyle(bodyStyle2);
                            } else {
                                cell2.setCellStyle(bodyStyle);
                            }
                            try {
                                cell2.setCellValue(Long.valueOf(strList.get(i)[j]));
                            } catch (Exception e) {
                                cell2.setCellValue(strList.get(i)[j]);
                            }
                        } else {
                            if (i % 2 == 0) {
                                cell2.setCellStyle(bodyStyle2);
                            } else {
                                cell2.setCellStyle(bodyStyle);
                            }
                            try {
                                cell2.setCellValue(Long.valueOf(strList.get(i)[j]));
                            } catch (Exception e) {
                                cell2.setCellValue(strList.get(i)[j]);
                            }
                        }
                    }
                }
                ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                SvgRasterizer.convertToPng(kpiRequest.get(index), byteArrayOut);
                index = index + 1;
                XSSFClientAnchor anchor = new XSSFClientAnchor(0, 600, 0, 800,
                        (short) 1, rowNum + strList.size() + 3, (short) 4, rowNum + strList.size() + 31);
                if (null != anchor) {
                    patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG));
                }
                rowNum = rowNum + 37 + strList.size();
                titleRowNum = titleRowNum + 37 + strList.size();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return workbook;
    }

    @ApiOperation("中台展示未生成满意度报告的常规报告")
    @RequestMapping(value = "/customReports", method = RequestMethod.GET)
    public JsonResponse listServiceReport(
            @ApiParam("登陆用户ID") @RequestParam(value = "loginUserId") Long loginUserId,
            @ApiParam("分页索引") @RequestParam(required = false, defaultValue = "1") int pageNum,
            @ApiParam("分页大小") @RequestParam(required = false, defaultValue = "10") int pageSize,
            @ApiParam("省份") @RequestParam(value = "provinces", required = false) String provinces,
            @ApiParam("品牌") @RequestParam(value = "brandname", required = false) String brandname,
            @ApiParam("维修站类型") @RequestParam(value = "category", required = false) String category,
            @ApiParam("服务开始时间") @RequestParam(value = "serviceStartTime", required = false) Long serviceStartTime,
            @ApiParam("服务结束时间") @RequestParam(value = "serviceEndTime", required = false) Long serviceEndTime,
            @ApiParam("开始时间") @RequestParam(value = "startTime", required = false) Long startTime,
            @ApiParam("结束时间") @RequestParam(value = "endTime", required = false) Long endTime,
            @ApiParam("服务项目") @RequestParam(value = "serviceType", required = false) String serviceType,
            @ApiParam("服务次数") @RequestParam(value = "serviceTimes", required = false, defaultValue = "0") int serviceTimes,
            @ApiParam("工作人员姓名") @RequestParam(value = "userName", required = false) String userName,
            @ApiParam("客户状态ID") @RequestParam(value = "customerStats", required = false) String customerStat,
            @ApiParam("客户类型ID") @RequestParam(value = "customerTypes", required = false) String customerTypes,
            @ApiParam("油漆品牌ID") @RequestParam(value = "paintBrand", required = false) String patintBrand,
            @ApiParam("品牌定位ID") @RequestParam(value = "productPosition", required = false) String productPosition,
            @ApiParam("维修站名称") @RequestParam(value = "stationId", required = false) Long stationId,
            @ApiParam("是否有待跟进") @RequestParam(value = "isFollowUp", required = false, defaultValue = "2") int isFollowUp,
            @ApiParam("部门ID") @RequestParam(value = "departmentIds", required = false) String departmentIds,
            @ApiParam("是否填写满意度报告") @RequestParam(value = "isCustomer", required = false, defaultValue = "2") int isCustomer,
            @ApiParam("满意度打分") @RequestParam(value = "score", required = false) String score,
            @ApiParam("是否生成满意度报告") @RequestParam(value = "isCustomReportGenerated", required = false, defaultValue = "0") int isCustomReportGenerated,
            @ApiParam("工作人员Id") @RequestParam(value = "userId", required = false) Long userId) {
        isUserLogin();
        User user = userRepository.findOne(loginUserId);
        if (user == null) {
            return JsonResponse.errorResponseWithError("抱歉", "未找到当前登陆用户");
        }
        SearchParamDTO searchParamDTO = SearchParamDTO.builder()
                .provinces(provinces)
                .brandNames(brandname)
                .categories(category)
                .serviceStartTime(serviceStartTime)
                .serviceEndTime(serviceEndTime)
                .startTime(startTime)
                .endTime(endTime)
                .serviceTypes(serviceType)
                .serviceTimes(serviceTimes)
                .userName(userName)
                .stationId(stationId)
                .customerStats(customerStat)
                .customerTypes(customerTypes)
                .paintBrands(patintBrand)
                .productPositions(productPosition)
                .isFollowUp(isFollowUp)
                .departmentIds(departmentIds)
                .isCustomer(isCustomer)
                .scores(score)
                .isCustomReportGenerated(isCustomReportGenerated)
                .userId(userId)
                .build();
        return JsonResponse.successResponseWithData(serviceReportService.listRegularReport(pageNum, pageSize, searchParamDTO));
    }

    @RequestMapping(value = "/customReport/workers")
    public JsonResponse getUsers(@ApiParam("省份") @RequestParam(value = "province", required = false) String province,
        @ApiParam("用户名名称") @RequestParam(value = "userName", required = false) String userName){
        isUserLogin();
        QUserProfile qUserProfile = QUserProfile.userProfile;
        BooleanBuilder builder = new BooleanBuilder();
        builder.and(qUserProfile.deleted.eq(false));
        builder.and(qUserProfile.enabled.eq(true));
        builder.and(qUserProfile.user.userType.ne(UserType.customer));
        if (StringUtils.isNotEmpty(userName)) {
            builder.and(qUserProfile.realName.like("%" + userName + "%"));
        }
        if (StringUtils.isNotEmpty(province)) {
            builder.and(qUserProfile.nationList.any().province.eq(province));
        }
        List<UserProfile> userProfileList = (List<UserProfile>) userProfileRepository.findAll(builder);
        List<LabelValue> labelValues = new ArrayList<>();
        if (null != userProfileList) {
            labelValues = toLableValue(userProfileList);
        }
        return JsonResponse.successResponseWithData(labelValues);
    }

    private List<LabelValue> toLableValue(List<UserProfile> userProfileList) {
        List<LabelValue> labelValues = new ArrayList<>();
        for (UserProfile userProfile : userProfileList) {
            LabelValue labelValue = new LabelValue(userProfile.getRealName(), userProfile.getUser().getId() + "");
            labelValues.add(labelValue);
        }
        return labelValues;
    }

    @ApiOperation("中台手动生成满意度报告并发送短信")
    @RequestMapping(value = "/customReport/generation", method = RequestMethod.PUT)
    public JsonResponse generateCustomReport(@RequestParam Long loginUserId, @RequestBody
            AddCustomReportDTO addCustomReportDTO) {
        isUserLogin();
        User user = userRepository.findOne(loginUserId);
        if (user == null) {
            return JsonResponse.errorResponseWithError("抱歉", "未找到当前登陆用户");
        }
        ModuleAuthority moduleAuthority = moduleAuthorityRepository.findByName(Constants.SEND_CUSTOM_REPORT_WRITING_SMS);
        boolean tf = user.getAuthorities().contains(moduleAuthority);
        if (tf == false) {
            return JsonResponse.errorResponseWithError("抱歉", "没有权限查看生成满意度报告（PC）");
        }
        return JsonResponse.successResponseWithData(serviceReportService.generateCustomReport(addCustomReportDTO.getIdList()));
    }


    @ApiOperation("常规服务报告导出")
    @RequestMapping(value = "getserviceReportOut", method = RequestMethod.POST)
    public JsonResponse getserviceReportOut(HttpServletRequest request, HttpServletResponse response,
                                            @RequestBody KpiResponseForOut kpiResponse,
                                            @ApiParam("登陆用户ID") @RequestParam(value = "loginUserId") Long loginUserId,
                                            @ApiParam("省份多选") @RequestParam(value = "provinces", required = false) String provinces,
                                            @ApiParam("品牌") @RequestParam(value = "brandname", required = false) String brandname,
                                            @ApiParam("维修站类型") @RequestParam(value = "category", required = false) String category,
                                            @ApiParam("维修站名称") @RequestParam(value = "name", required = false) String name,
                                            @ApiParam("服务开始时间") @RequestParam(value = "serviceStartTime", required = false) Long serviceStartTime,
                                            @ApiParam("服务结束时间") @RequestParam(value = "serviceEndTime", required = false) Long serviceEndTime,
                                            @ApiParam("开始时间") @RequestParam(value = "startTime", required = false) Long startTime,
                                            @ApiParam("结束时间") @RequestParam(value = "endTime", required = false) Long endTime,
                                            @ApiParam("服务项目") @RequestParam(value = "serviceType", required = false) Long serviceType,
                                            @ApiParam("技术人员姓名") @RequestParam(value = "userName", required = false) String userName,
                                            @ApiParam("是否填写满意度报告") @RequestParam(value = "isCustomer", required = false) Boolean isCustomer,
                                            @ApiParam("客户状态ID") @RequestParam(value = "customerStat", required = false) Long customerStat,
                                            @ApiParam("客户类型ID") @RequestParam(value = "customerType", required = false) Long customerType,
                                            @ApiParam("油漆品牌ID") @RequestParam(value = "paintBrand", required = false) String patintBrand,
                                            @ApiParam("品牌定位ID") @RequestParam(value = "productPosition", required = false) String productPosition,
                                            @ApiParam(value = "维修站编号") @RequestParam(value = "stationNum", required = false) String stationNum,
                                            @ApiParam(value = "部门ID") @RequestParam(value = "departmentId", required = false) Long departmentId) throws Exception {
        isUserLogin();
        VstarServiceReport serviceReport = (VstarServiceReport) serviceReportStat(loginUserId, provinces, brandname, category, name, serviceStartTime, serviceEndTime, startTime, endTime, serviceType, userName, isCustomer,
                customerStat, customerType, patintBrand, productPosition, stationNum, departmentId).getData();
        String templetPath = request.getSession().getServletContext().getRealPath("");
        ServiceResponseVo serviceResponseVo = new ServiceResponseVo();
        serviceResponseVo.setKpiResponse(kpiResponse.getKpiResponse());
        serviceResponseVo.setServiceReport(serviceReport);
        String filePath = exportReportService.getServiceReport(serviceResponseVo, templetPath);

//        导出日志============================================
        try {
            Map<String, Object> searchParams = new HashMap<>();
            searchParams.put("省份", provinces);
            searchParams.put("品牌", brandname);
            searchParams.put("维修站类型", category);
            searchParams.put("维修站名称", name);
            if (null != serviceStartTime) {
                searchParams.put("服务开始时间", com.ugoodtech.mdcc.core.utils.DateUtil.formatDate(new Date(serviceStartTime), Constants.DATE_FORMAT));
            }
            if (null != serviceEndTime) {
                searchParams.put("服务结束时间", DateUtil.formatDate(new Date(serviceEndTime), Constants.DATE_FORMAT));
            }
            if (null != startTime) {
                searchParams.put("开始时间", com.ugoodtech.mdcc.core.utils.DateUtil.formatDate(new Date(startTime), Constants.DATE_FORMAT));
            }
            if (null != endTime) {
                searchParams.put("结束时间", DateUtil.formatDate(new Date(endTime), Constants.DATE_FORMAT));
            }
            if (null != serviceType) {
                QuestionAnswerOption questionAnswerOption = questionAnswerOptionRepository.findOne(serviceType);
                searchParams.put("服务项目", questionAnswerOption.getName());
            }
            searchParams.put("技术人员姓名", userName);
            if (null != isCustomer) {
                if (isCustomer) {
                    searchParams.put("是否填写满意度报告", "是");
                } else {
                    searchParams.put("是否填写满意度报告", "否");
                }
            }
            if (null != customerStat) {
                QuestionAnswerOption questionAnswerOption = questionAnswerOptionRepository.findOne(customerStat);
                searchParams.put("客户状态", questionAnswerOption.getName());
            }
            if (null != customerType) {
                QuestionAnswerOption questionAnswerOption = questionAnswerOptionRepository.findOne(customerType);
                searchParams.put("客户类型", questionAnswerOption.getName());
            }
            if (null != patintBrand) {
                QuestionAnswerOption questionAnswerOption = questionAnswerOptionRepository.findOne(Long.parseLong(patintBrand));
                searchParams.put("油漆品牌", questionAnswerOption.getName());
            }
            if (null != productPosition) {
                QuestionAnswerOption questionAnswerOption = questionAnswerOptionRepository.findOne(Long.parseLong(productPosition));
                searchParams.put("品牌定位", questionAnswerOption.getName());
            }
            searchParams.put("维修站编号", stationNum);
            String desc = "筛选了:";
            for (String key : searchParams.keySet()) {
                if (null != searchParams.get(key) && !StringUtils.isEmpty(searchParams.get(key).toString())) {
                    desc += (key + "=>" + searchParams.get(key) + " ,");
                }
            }
            if ("筛选了:".equals(desc)) {
                desc = "";
            }
            Map<String, Object> data = new HashMap<>();
            data.put("str", desc);
            userLogService.addUserLog(this.getCurrentUser(), null,
                    LogConstants.operate_type_send_export,
                    LogConstants.business_type_regular_report_statistics, null, data);
        } catch (Exception e) {
            e.printStackTrace();
        }
        filePath = Constants.v_starServiceUrl + filePath;
//        filePath = "http://vr.duluxdesigner.com.cn" + filePath;
//        filePath = "http://116.62.64.140:51002" + filePath;
        return JsonResponse.successResponseWithData(filePath);
    }

    @ApiOperation("常规服务报告导出--EXCEL")
    @RequestMapping(value = "exportExcelOut", method = RequestMethod.POST)
    public JsonResponse exportExcelOut(HttpServletRequest request, HttpServletResponse response,
                                       @RequestBody KpiResponseForOut kpiResponse,
                                       @ApiParam("登陆用户ID") @RequestParam(value = "loginUserId") Long loginUserId,
                                       @ApiParam("省份多选") @RequestParam(value = "provinces", required = false) String provinces,
                                       @ApiParam("品牌") @RequestParam(value = "brandname", required = false) String brandname,
                                       @ApiParam("维修站类型") @RequestParam(value = "category", required = false) String category,
                                       @ApiParam("维修站名称") @RequestParam(value = "name", required = false) String name,
                                       @ApiParam("服务开始时间") @RequestParam(value = "serviceStartTime", required = false) Long serviceStartTime,
                                       @ApiParam("服务结束时间") @RequestParam(value = "serviceEndTime", required = false) Long serviceEndTime,
                                       @ApiParam("开始时间") @RequestParam(value = "startTime", required = false) Long startTime,
                                       @ApiParam("结束时间") @RequestParam(value = "endTime", required = false) Long endTime,
                                       @ApiParam("服务项目") @RequestParam(value = "serviceType", required = false) Long serviceType,
                                       @ApiParam("技术人员姓名") @RequestParam(value = "userName", required = false) String userName,
                                       @ApiParam("是否填写满意度报告") @RequestParam(value = "isCustomer", required = false) Boolean isCustomer,
                                       @ApiParam("客户状态ID") @RequestParam(value = "customerStat", required = false) Long customerStat,
                                       @ApiParam("客户类型ID") @RequestParam(value = "customerType", required = false) Long customerType,
                                       @ApiParam("油漆品牌ID") @RequestParam(value = "paintBrand", required = false) String patintBrand,
                                       @ApiParam("品牌定位ID") @RequestParam(value = "productPosition", required = false) String productPosition,
                                       @ApiParam(value = "维修站编号") @RequestParam(value = "stationNum", required = false) String stationNum,
                                       @ApiParam(value = "部门ID") @RequestParam(value = "departmentId", required = false) Long departmentId) throws Exception {
        isUserLogin();
        VstarServiceReport serviceReport = (VstarServiceReport) serviceReportStat(loginUserId, provinces, brandname, category, name, serviceStartTime, serviceEndTime, startTime, endTime, serviceType, userName, isCustomer,
                customerStat, customerType, patintBrand, productPosition, stationNum, departmentId).getData();
        //常规报告数据分析
        ServiceResponseVo serviceReportStat = new ServiceResponseVo();
        serviceReportStat.setKpiResponse(kpiResponse.getKpiResponse());
        serviceReportStat.setServiceReport(serviceReport);
        //常规报告列表
//        VstarUserKpiReport vstarUserKpiReport = (VstarUserKpiReport) weekServiceReportServiceController.getWeekReport(loginUserId, provinces, brandname, category, serviceStartTime, serviceEndTime, serviceType, userName, name,
//                customerStat, customerType, patintBrand, productPosition, stationNum, null, null).getData();
        String templetPath = request.getSession().getServletContext().getRealPath("");
        String fileName = "常规报告数据统计.xlsx";
        //sheet名
        String sheetName = "常规报告数据统计";
        XSSFWorkbook wb = getServiceReportExcel(sheetName, null, serviceReportStat, null);
        //响应到客户端
        try {
            FileOutputStream outputStream = new FileOutputStream(new File(templetPath + "/export/" + fileName));
            wb.write(outputStream);
            outputStream.close();
            //导出日志============================================
            try {
                Map<String, Object> searchParams = new HashMap<>();
                searchParams.put("省份", provinces);
                searchParams.put("品牌", brandname);
                searchParams.put("维修站类型", category);
                searchParams.put("维修站名称", name);
                if (null != serviceStartTime) {
                    searchParams.put("服务开始时间", com.ugoodtech.mdcc.core.utils.DateUtil.formatDate(new Date(serviceStartTime), Constants.DATE_FORMAT));
                }
                if (null != serviceEndTime) {
                    searchParams.put("服务结束时间", DateUtil.formatDate(new Date(serviceEndTime), Constants.DATE_FORMAT));
                }
                if (null != startTime) {
                    searchParams.put("开始时间", com.ugoodtech.mdcc.core.utils.DateUtil.formatDate(new Date(startTime), Constants.DATE_FORMAT));
                }
                if (null != endTime) {
                    searchParams.put("结束时间", DateUtil.formatDate(new Date(endTime), Constants.DATE_FORMAT));
                }
                if (null != serviceType) {
                    QuestionAnswerOption questionAnswerOption = questionAnswerOptionRepository.findOne(serviceType);
                    searchParams.put("服务项目", questionAnswerOption.getName());
                }
                searchParams.put("技术人员姓名", userName);
                if (null != isCustomer) {
                    if (isCustomer) {
                        searchParams.put("是否填写满意度报告", "是");
                    } else {
                        searchParams.put("是否填写满意度报告", "否");
                    }
                }
                if (null != customerStat) {
                    QuestionAnswerOption questionAnswerOption = questionAnswerOptionRepository.findOne(customerStat);
                    searchParams.put("客户状态", questionAnswerOption.getName());
                }
                if (null != customerType) {
                    QuestionAnswerOption questionAnswerOption = questionAnswerOptionRepository.findOne(customerType);
                    searchParams.put("客户类型", questionAnswerOption.getName());
                }
                if (null != patintBrand) {
                    QuestionAnswerOption questionAnswerOption = questionAnswerOptionRepository.findOne(Long.parseLong(patintBrand));
                    searchParams.put("油漆品牌", questionAnswerOption.getName());
                }
                if (null != productPosition) {
                    QuestionAnswerOption questionAnswerOption = questionAnswerOptionRepository.findOne(Long.parseLong(productPosition));
                    searchParams.put("品牌定位", questionAnswerOption.getName());
                }
                searchParams.put("维修站编号", stationNum);
                String desc = "筛选了:";
                for (String key : searchParams.keySet()) {
                    if (null != searchParams.get(key) && !StringUtils.isEmpty(searchParams.get(key).toString())) {
                        desc += (key + "=>" + searchParams.get(key) + " ,");
                    }
                }
                if ("筛选了:".equals(desc)) {
                    desc = "";
                }
                Map<String, Object> data = new HashMap<>();
                data.put("str", desc);
                userLogService.addUserLog(this.getCurrentUser(), null,
                        LogConstants.operate_type_send_export,
                        LogConstants.business_type_regular_report_statistics, null, data);
            } catch (Exception e) {
                e.printStackTrace();
            }


            String filePath = Constants.v_starServiceUrl + "/export/" + fileName;
//            String filePath = "http://116.62.64.140:51002/export/" + fileName;
//            filePath = "http://116.62.64.140:51002" + filePath;
            return JsonResponse.successResponseWithData(filePath);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return JsonResponse.successResponse();
    }

    public XSSFWorkbook getServiceReportExcel(String sheetName, XSSFWorkbook workbook, ServiceResponseVo serviceResponseVo, VstarCustomerSatisfactionReport vstarCustomerSatisfactionReport)
            throws Exception {
        List<String> svgList = serviceResponseVo.getKpiResponse();
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (workbook == null) {
            workbook = new XSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = workbook.createSheet(sheetName);
        XSSFDrawing patriarch = sheet.createDrawingPatriarch();
        XSSFCellStyle titleStyle6 = workbook.createCellStyle();
        XSSFFont font3 = workbook.createFont();
        font3.setFontName("微软雅黑");
        font3.setFontHeightInPoints((short) 16);
        font3.setBold(true);
        titleStyle6.setFont(font3);
        titleStyle6.setAlignment(HorizontalAlignment.CENTER);
        titleStyle6.setVerticalAlignment(VerticalAlignment.CENTER);
        XSSFRow rowIndex0 = sheet.createRow(0);
        XSSFCell provinces = rowIndex0.createCell(8);
        provinces.setCellStyle(titleStyle6);
        provinces.setCellValue("销售区域");
        XSSFCell customerStat = rowIndex0.createCell(18);
        customerStat.setCellStyle(titleStyle6);
        customerStat.setCellValue("客户状态");


        XSSFRow rowIndex1 = sheet.createRow(33);
        XSSFCell customerType = rowIndex1.createCell(8);
        customerType.setCellStyle(titleStyle6);
        customerType.setCellValue("客户类型");
        XSSFCell stationType = rowIndex1.createCell(18);
        stationType.setCellStyle(titleStyle6);
        stationType.setCellValue("维修站类型");

        XSSFRow rowIndex2 = sheet.createRow(68);
        XSSFCell stationBrand = rowIndex2.createCell(9);
        stationBrand.setCellStyle(titleStyle6);
        stationBrand.setCellValue("维修站具体品牌");


        XSSFRow rowIndex3 = sheet.createRow(102);
        XSSFCell paintBrand = rowIndex3.createCell(8);
        paintBrand.setCellStyle(titleStyle6);
        paintBrand.setCellValue("油漆品牌");//
        XSSFCell productPosition = rowIndex3.createCell(18);
        productPosition.setCellStyle(titleStyle6);
        productPosition.setCellValue("品牌定位");


        XSSFRow rowIndex4 = sheet.createRow(136);
        XSSFCell service = rowIndex4.createCell(10);
        service.setCellStyle(titleStyle6);
        service.setCellValue("服务类型");//


        XSSFRow rowIndex5 = sheet.createRow(165);
        XSSFCell repair = rowIndex5.createCell(7);
        repair.setCellStyle(titleStyle6);
        repair.setCellValue("钣喷维修流程中面对最大问题");
        XSSFCell ask = rowIndex5.createCell(18);
        ask.setCellStyle(titleStyle6);
        ask.setCellValue("维修站主要诉求");

        try {
            logger.info("svg长度为：" + svgList.size());
            double standardWidth = 800;
            double standardHeight = 500;
            double a = 0;
            double b = 0;
            for (int i = 0; i < svgList.size(); i++) {
                ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                WordUtil.convertToPng(svgList.get(i), byteArrayOut);
//                XSSFClientAnchor anchor = helper.createClientAnchor();
                XSSFClientAnchor anchor = null;
                int pictureIdx = workbook.addPicture(byteArrayOut.toByteArray(), Workbook.PICTURE_TYPE_PNG);
                if (i == 0) {
                    logger.info("生成销售区域图表");
//                    anchor.setCol1(0);
//                    anchor.setRow1(1);
//                    double cellWidth = sheet.getColumnWidthInPixels(0);
//                    XSSFRow row = sheet.createRow(1);
//                    double cellHeight = row.getHeightInPoints() / 72 * 96;
//                    a = standardWidth / cellWidth;
//                    b = standardHeight / cellHeight;
                    anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 0, 1, (short) 12, 32);
                } else if (i == 1) {
                    logger.info("生成客户状态图表");
                    anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 14, 1, (short) 24, 32);
                } else if (i == 2) {
                    logger.info("生成客户类型图表");
                    anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 0, 35, (short) 10, 58);
                } else if (i == 10) {
                    logger.info("生成维修站类型图表");
                    anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 14, 35, (short) 24, 65);
                } else if (i == 3) {
                    logger.info("生成维修站具体品牌图表");
                    anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 0, 69, (short) 12, 100);
                } else if (i == 4) {
                    logger.info("生成油漆品牌图表");
                    anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 0, 103, (short) 10, 133);
                } else if (i == 5) {
                    logger.info("生成品牌定位图表");
                    anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 14, 103, (short) 24, 133);
                } else if (i == 8) {
                    logger.info("生成服务类型图表");
                    anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 0, 137, (short) 12, 163);
                } else if (i == 6) {
                    logger.info("生成钣喷维修流程中面对最大问题图表");
                    anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 0, 166, (short) 10, 196);
                } else if (i == 7) {
                    logger.info("生成维修站主要诉求图表");
                    anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 14, 166, (short) 24, 196);
                }
                if (null != anchor) {
//                    anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
                    patriarch.createPicture(anchor, pictureIdx);
                }
            }


            /**
             * 第二个sheet
             */
            XSSFDataFormat xssfDataFormat = workbook.createDataFormat();
//
            XSSFFont font = workbook.createFont();
            font.setFontName("微软雅黑");
            font.setFontHeightInPoints((short) 10);
            font.setColor(IndexedColors.WHITE.getIndex());
            XSSFCellStyle titleStyle = workbook.createCellStyle();
            // 创建一个居中格式
            XSSFColor myColor = new XSSFColor(new java.awt.Color(192, 80, 77));
            titleStyle.setFillForegroundColor(myColor);
            titleStyle.setAlignment(HorizontalAlignment.CENTER);
            titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle.setFont(font);
            titleStyle.setBorderLeft(BorderStyle.THIN);
            titleStyle.setLeftBorderColor(myColor);
            titleStyle.setBorderRight(BorderStyle.THIN);
            titleStyle.setRightBorderColor(myColor);
            titleStyle.setBorderTop(BorderStyle.THIN);
            titleStyle.setTopBorderColor(myColor);

            XSSFColor myColor1 = new XSSFColor(new java.awt.Color(230, 184, 183));
            XSSFCellStyle titleStyle2 = workbook.createCellStyle();
            titleStyle2.setAlignment(HorizontalAlignment.CENTER);
            titleStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle2.setFillForegroundColor(myColor1);
            titleStyle2.setBorderTop(BorderStyle.THIN);
            titleStyle2.setBorderBottom(BorderStyle.THIN);
            titleStyle2.setBorderRight(BorderStyle.THIN);
            titleStyle2.setTopBorderColor(myColor1);
            XSSFFont font2 = workbook.createFont();
            font2.setFontName("微软雅黑");
            font2.setFontHeightInPoints((short) 10);
            titleStyle2.setFont(font2);


            XSSFCellStyle titleStyle3 = workbook.createCellStyle();
            titleStyle3.setAlignment(HorizontalAlignment.CENTER);
            titleStyle3.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle3.setFillForegroundColor(myColor1);
            titleStyle3.setBorderTop(BorderStyle.THIN);
            titleStyle3.setBorderBottom(BorderStyle.THIN);
            titleStyle3.setBorderRight(BorderStyle.NONE);
            titleStyle3.setTopBorderColor(myColor1);
            titleStyle3.setFont(font2);

            XSSFColor myColor2 = new XSSFColor(new java.awt.Color(242, 220, 219));
            XSSFCellStyle titleStyle4 = workbook.createCellStyle();
            titleStyle4.setAlignment(HorizontalAlignment.CENTER);
            titleStyle4.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle4.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle4.setFillForegroundColor(myColor2);
            titleStyle4.setBorderTop(BorderStyle.THIN);
            titleStyle4.setBorderBottom(BorderStyle.THIN);
            titleStyle4.setBorderRight(BorderStyle.NONE);
            titleStyle4.setTopBorderColor(myColor2);
            titleStyle4.setFont(font2);

            XSSFCellStyle titleStyle5 = workbook.createCellStyle();
            titleStyle5.setAlignment(HorizontalAlignment.CENTER);
            titleStyle5.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle5.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle5.setFillForegroundColor(myColor2);
            titleStyle5.setBorderTop(BorderStyle.THIN);
            titleStyle5.setBorderBottom(BorderStyle.THIN);
            titleStyle5.setBorderRight(BorderStyle.THIN);
            titleStyle5.setTopBorderColor(myColor2);
            titleStyle5.setFont(font2);


            XSSFSheet sheet1 = workbook.createSheet("常规报告数据表");
            for (int i = 0; i < 11; i++) {
                sheet1.setColumnWidth(i, 8 * 2 * 256);
            }

            XSSFRow sheet1Row1 = sheet1.createRow(0);
            List<String[]> provinceList = serviceResponseVo.getServiceReport().getProvinceLsit();
            // 销售省份
            XSSFCell sheet1Cell = null;
            String[] sheet1Provices = {"销售省份", "站点数量", "比例"};
            //创建标题
            for (int i = 0; i < sheet1Provices.length; i++) {
                sheet1Cell = sheet1Row1.createCell(i);
                sheet1Cell.setCellValue(sheet1Provices[i]);
                sheet1Cell.setCellStyle(titleStyle);
            }

            int countValue = serviceResponseVo.getServiceReport().getCountPerson();
//            for (int i = 0; i < provinceList.size(); i++) {
//                countValue += Integer.valueOf(provinceList.get(i)[1]);
//            }
            for (int i = 0; i < provinceList.size(); i++) {
                provinceList.get(i)[2] = doubleGeTowDecimal(Double.valueOf(provinceList.get(i)[1]) / countValue);
            }
            String[] strings = new String[3];
            strings[0] = "有效报告数";
            strings[1] = String.valueOf(countValue);
            strings[2] = "1";
            provinceList.add(strings);

            XSSFCell sheet1Cell2 = null;
            for (int i = 0; i < provinceList.size(); i++) {
                sheet1Row1 = sheet1.createRow(i + 1);
                for (int j = 0; j < provinceList.get(i).length; j++) {
                    sheet1Cell2 = sheet1Row1.createCell(j);
                    //将内容按顺序赋给对应的列对象
                    Double doubleValue = 0D;
                    if (j == provinceList.get(i).length - 1) {
                        if (i % 2 == 0) {
                            if (j == 2) {
                                doubleValue = Double.valueOf(provinceList.get(i)[j]);
                                titleStyle2.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                            }
                            sheet1Cell2.setCellStyle(titleStyle2);
                        } else {
                            if (j == 2) {
                                if (StringUtils.isNotEmpty(provinceList.get(i)[j])) {
                                    doubleValue = Double.valueOf(provinceList.get(i)[j]);
                                    titleStyle5.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                            }
                            sheet1Cell2.setCellStyle(titleStyle5);
                        }
                        try {
                            if (j == 2) {
                                sheet1Cell2.setCellValue(doubleValue);
                            } else {
                                sheet1Cell2.setCellValue(Long.valueOf(provinceList.get(i)[j]));
                            }
                        } catch (Exception e) {
                            sheet1Cell2.setCellValue(provinceList.get(i)[j]);
                        }
                    } else {
                        if (i % 2 == 0) {
                            if (j == 2) {
                                doubleValue = Double.valueOf(provinceList.get(i)[j]);
                                titleStyle3.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                            }
                            sheet1Cell2.setCellStyle(titleStyle3);
                        } else {
                            if (j == 2) {
                                if (StringUtils.isNotEmpty(provinceList.get(i)[j])) {
                                    doubleValue = Double.valueOf(provinceList.get(i)[j]);
                                    titleStyle4.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                            }
                            sheet1Cell2.setCellStyle(titleStyle4);
                        }
                        try {
                            if (j == 2) {
                                sheet1Cell2.setCellValue(doubleValue);
                            } else {
                                sheet1Cell2.setCellValue(Long.valueOf(provinceList.get(i)[j]));
                            }
                        } catch (Exception e) {
                            sheet1Cell2.setCellValue(provinceList.get(i)[j]);
                        }
                    }
                }
            }

            // 客户状态
            XSSFColor myColor3 = new XSSFColor(new java.awt.Color(75, 172, 198));
            XSSFCellStyle titleStyle7 = workbook.createCellStyle();
            titleStyle7.setFillForegroundColor(myColor3);
            titleStyle7.setAlignment(HorizontalAlignment.CENTER);
            titleStyle7.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle7.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle7.setFont(font);
            titleStyle7.setBorderLeft(BorderStyle.THIN);
            titleStyle7.setLeftBorderColor(myColor3);
            titleStyle7.setBorderRight(BorderStyle.THIN);
            titleStyle7.setRightBorderColor(myColor3);
            titleStyle7.setBorderTop(BorderStyle.THIN);
            titleStyle7.setTopBorderColor(myColor3);

            XSSFColor myColor4 = new XSSFColor(new java.awt.Color(183, 222, 232));
            XSSFCellStyle titleStyle22 = workbook.createCellStyle();
            titleStyle22.setAlignment(HorizontalAlignment.CENTER);
            titleStyle22.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle22.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle22.setFillForegroundColor(myColor4);
            titleStyle22.setBorderTop(BorderStyle.THIN);
            titleStyle22.setBorderBottom(BorderStyle.THIN);
            titleStyle22.setBorderRight(BorderStyle.THIN);
            titleStyle22.setTopBorderColor(myColor4);
            titleStyle2.setFont(font2);


            XSSFCellStyle titleStyle33 = workbook.createCellStyle();
            titleStyle33.setAlignment(HorizontalAlignment.CENTER);
            titleStyle33.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle33.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle33.setFillForegroundColor(myColor4);
            titleStyle33.setBorderTop(BorderStyle.THIN);
            titleStyle33.setBorderBottom(BorderStyle.THIN);
            titleStyle33.setBorderRight(BorderStyle.NONE);
            titleStyle33.setTopBorderColor(myColor4);
            titleStyle33.setFont(font2);

            XSSFColor myColor5 = new XSSFColor(new java.awt.Color(218, 238, 243));
            XSSFCellStyle titleStyle44 = workbook.createCellStyle();
            titleStyle44.setAlignment(HorizontalAlignment.CENTER);
            titleStyle44.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle44.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle44.setFillForegroundColor(myColor5);
            titleStyle44.setBorderTop(BorderStyle.THIN);
            titleStyle44.setBorderBottom(BorderStyle.THIN);
            titleStyle44.setBorderRight(BorderStyle.NONE);
            titleStyle44.setTopBorderColor(myColor5);
            titleStyle44.setFont(font2);

            XSSFCellStyle titleStyle55 = workbook.createCellStyle();
            titleStyle55.setAlignment(HorizontalAlignment.CENTER);
            titleStyle55.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle55.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle55.setFillForegroundColor(myColor5);
            titleStyle55.setBorderTop(BorderStyle.THIN);
            titleStyle55.setBorderBottom(BorderStyle.THIN);
            titleStyle55.setBorderRight(BorderStyle.THIN);
            titleStyle55.setTopBorderColor(myColor5);
            titleStyle55.setFont(font2);

            List<String[]> customerStatList = serviceResponseVo.getServiceReport().getCustomerStatList();
            XSSFCell sheet1Cell1 = null;
            String[] sheet1CustomerStat = {"客户状态", "站点数量", "比例"};
            XSSFRow sheet1Row2 = sheet1.createRow(provinceList.size() + 4);
            //创建标题
            for (int i = 0; i < sheet1CustomerStat.length; i++) {
                sheet1Cell1 = sheet1Row2.createCell(i);
                sheet1Cell1.setCellValue(sheet1CustomerStat[i]);
                sheet1Cell1.setCellStyle(titleStyle7);
            }

            countValue = serviceResponseVo.getServiceReport().getCountPerson();
//            for (int i = 0; i < provinceList.size(); i++) {
//                countValue += Integer.valueOf(provinceList.get(i)[1]);
//            }
            for (int i = 0; i < customerStatList.size(); i++) {
                customerStatList.get(i)[2] = doubleGeTowDecimal(Double.valueOf(customerStatList.get(i)[1]) / countValue);
            }
            strings = new String[3];
            strings[0] = "有效报告数";
            strings[1] = String.valueOf(countValue);
            strings[2] = "1";
            customerStatList.add(strings);

            XSSFCell sheet1Cell3 = null;
            for (int i = 0; i < customerStatList.size(); i++) {
                sheet1Row2 = sheet1.createRow(i + provinceList.size() + 5);
                for (int j = 0; j < customerStatList.get(i).length; j++) {
                    sheet1Cell3 = sheet1Row2.createCell(j);
                    //将内容按顺序赋给对应的列对象
                    Double doubleValue = 0D;
                    if (j == customerStatList.get(i).length - 1) {
                        if (i % 2 == 0) {
                            if (j == 2) {
                                doubleValue = Double.valueOf(customerStatList.get(i)[j]);
                                titleStyle22.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                            }
                            sheet1Cell3.setCellStyle(titleStyle22);
                        } else {
                            if (j == 2) {
                                if (StringUtils.isNotEmpty(customerStatList.get(i)[j])) {
                                    doubleValue = Double.valueOf(customerStatList.get(i)[j]);
                                    titleStyle55.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                            }
                            sheet1Cell3.setCellStyle(titleStyle55);
                        }
                        try {
                            if (j == 2) {
                                sheet1Cell3.setCellValue(doubleValue);
                            } else {
                                sheet1Cell3.setCellValue(Long.valueOf(customerStatList.get(i)[j]));
                            }
                        } catch (Exception e) {
                            sheet1Cell3.setCellValue(customerStatList.get(i)[j]);
                        }

                    } else {
                        if (i % 2 == 0) {
                            if (j == 2) {
                                doubleValue = Double.valueOf(customerStatList.get(i)[j]);
                                titleStyle33.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                            }
                            sheet1Cell3.setCellStyle(titleStyle33);
                        } else {
                            if (j == 2) {
                                if (StringUtils.isNotEmpty(customerStatList.get(i)[j])) {
                                    doubleValue = Double.valueOf(customerStatList.get(i)[j]);
                                    titleStyle44.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                            }
                            sheet1Cell3.setCellStyle(titleStyle44);
                        }
                        try {
                            if (j == 2) {
                                sheet1Cell3.setCellValue(doubleValue);
                            } else {
                                sheet1Cell3.setCellValue(Long.valueOf(customerStatList.get(i)[j]));
                            }
                        } catch (Exception e) {
                            sheet1Cell3.setCellValue(customerStatList.get(i)[j]);
                        }
                    }
                }
            }

            // 客户类型
            XSSFColor myColor6 = new XSSFColor(new java.awt.Color(247, 150, 70));
            XSSFCellStyle titleStyle8 = workbook.createCellStyle();
            titleStyle8.setFillForegroundColor(myColor6);
            titleStyle8.setAlignment(HorizontalAlignment.CENTER);
            titleStyle8.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle8.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle8.setFont(font);
            titleStyle8.setBorderLeft(BorderStyle.THIN);
            titleStyle8.setLeftBorderColor(myColor6);
            titleStyle8.setBorderRight(BorderStyle.THIN);
            titleStyle8.setRightBorderColor(myColor6);
            titleStyle8.setBorderTop(BorderStyle.THIN);
            titleStyle8.setTopBorderColor(myColor6);

            XSSFColor myColor7 = new XSSFColor(new java.awt.Color(252, 213, 180));
            XSSFCellStyle titleStyle222 = workbook.createCellStyle();
            titleStyle222.setAlignment(HorizontalAlignment.CENTER);
            titleStyle222.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle222.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle222.setFillForegroundColor(myColor7);
            titleStyle222.setBorderTop(BorderStyle.THIN);
            titleStyle222.setBorderBottom(BorderStyle.THIN);
            titleStyle222.setBorderRight(BorderStyle.THIN);
            titleStyle222.setTopBorderColor(myColor7);
            titleStyle222.setFont(font2);


            XSSFCellStyle titleStyle333 = workbook.createCellStyle();
            titleStyle333.setAlignment(HorizontalAlignment.CENTER);
            titleStyle333.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle333.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle333.setFillForegroundColor(myColor7);
            titleStyle333.setBorderTop(BorderStyle.THIN);
            titleStyle333.setBorderBottom(BorderStyle.THIN);
            titleStyle333.setBorderRight(BorderStyle.NONE);
            titleStyle333.setTopBorderColor(myColor7);
            titleStyle333.setFont(font2);

            XSSFColor myColor8 = new XSSFColor(new java.awt.Color(253, 223, 217));
            XSSFCellStyle titleStyle444 = workbook.createCellStyle();
            titleStyle444.setAlignment(HorizontalAlignment.CENTER);
            titleStyle444.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle444.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle444.setFillForegroundColor(myColor8);
            titleStyle444.setBorderTop(BorderStyle.THIN);
            titleStyle444.setBorderBottom(BorderStyle.THIN);
            titleStyle444.setBorderRight(BorderStyle.NONE);
            titleStyle444.setTopBorderColor(myColor8);
            titleStyle444.setFont(font2);

            XSSFCellStyle titleStyle555 = workbook.createCellStyle();
            titleStyle555.setAlignment(HorizontalAlignment.CENTER);
            titleStyle555.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle555.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle555.setFillForegroundColor(myColor8);
            titleStyle555.setBorderTop(BorderStyle.THIN);
            titleStyle555.setBorderBottom(BorderStyle.THIN);
            titleStyle555.setBorderRight(BorderStyle.THIN);
            titleStyle555.setTopBorderColor(myColor8);
            titleStyle555.setFont(font2);

            List<String[]> customerTypeList = serviceResponseVo.getServiceReport().getCustomerTypeList();
            XSSFCell sheet1Cell4 = null;
            String[] sheet1CustomerType = {"客户状态", "站点数量", "比例"};
            XSSFRow sheet1Row3 = sheet1.createRow(provinceList.size() + 4 + customerStatList.size() + 4);
            //创建标题
            for (int i = 0; i < sheet1CustomerType.length; i++) {
                sheet1Cell4 = sheet1Row3.createCell(i);
                sheet1Cell4.setCellValue(sheet1CustomerType[i]);
                sheet1Cell4.setCellStyle(titleStyle8);
            }

            countValue = serviceResponseVo.getServiceReport().getCountPerson();
//            for (int i = 0; i < provinceList.size(); i++) {
//                countValue += Integer.valueOf(provinceList.get(i)[1]);
//            }
            for (int i = 0; i < customerTypeList.size(); i++) {
                customerTypeList.get(i)[2] = doubleGeTowDecimal(Double.valueOf(customerTypeList.get(i)[1]) / countValue);
            }
            strings = new String[3];
            strings[0] = "有效报告数";
            strings[1] = String.valueOf(countValue);
            strings[2] = "1";
            customerTypeList.add(strings);

            XSSFCell sheet1Cell5 = null;
            for (int i = 0; i < customerTypeList.size(); i++) {
                sheet1Row3 = sheet1.createRow(i + provinceList.size() + 5 + customerStatList.size() + 4);
                for (int j = 0; j < customerTypeList.get(i).length; j++) {
                    sheet1Cell5 = sheet1Row3.createCell(j);
                    //将内容按顺序赋给对应的列对象
                    Double doubleValue = 0D;
                    if (j == customerTypeList.get(i).length - 1) {
                        if (i % 2 == 0) {
                            if (j == 2) {
                                doubleValue = Double.valueOf(customerTypeList.get(i)[j]);
                                titleStyle222.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                            }
                            sheet1Cell5.setCellStyle(titleStyle222);
                        } else {
                            if (j == 2) {
                                if (StringUtils.isNotEmpty(customerTypeList.get(i)[j])) {
                                    doubleValue = Double.valueOf(customerTypeList.get(i)[j]);
                                    titleStyle555.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                            }
                            sheet1Cell5.setCellStyle(titleStyle555);
                        }
                        try {
                            if (j == 2) {
                                sheet1Cell5.setCellValue(doubleValue);
                            } else {
                                sheet1Cell5.setCellValue(Long.valueOf(customerTypeList.get(i)[j]));
                            }
                        } catch (Exception e) {
                            sheet1Cell5.setCellValue(customerTypeList.get(i)[j]);
                        }

                    } else {
                        if (i % 2 == 0) {
                            if (j == 2) {
                                doubleValue = Double.valueOf(customerTypeList.get(i)[j]);
                                titleStyle333.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                            }
                            sheet1Cell5.setCellStyle(titleStyle333);
                        } else {
                            if (j == 2) {
                                if (StringUtils.isNotEmpty(customerTypeList.get(i)[j])) {
                                    doubleValue = Double.valueOf(customerTypeList.get(i)[j]);
                                    titleStyle444.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                            }
                            sheet1Cell5.setCellStyle(titleStyle444);
                        }
                        try {
                            if (j == 2) {
                                sheet1Cell5.setCellValue(doubleValue);
                            } else {
                                sheet1Cell5.setCellValue(Long.valueOf(customerTypeList.get(i)[j]));
                            }
                        } catch (Exception e) {
                            sheet1Cell5.setCellValue(customerTypeList.get(i)[j]);
                        }
                    }
                }
            }

            //其他客户类型
            List<String[]> otherCustomerTypeList = serviceResponseVo.getServiceReport().getOtherCustomerTypeList();
            if (otherCustomerTypeList != null && otherCustomerTypeList.size() > 0) {
                XSSFCell sheet1Cell6 = null;
                String[] sheet1OtherCustomerType = {"其他客户状态", "站点数量", "比例"};
                XSSFRow sheet1Row4 = sheet1.createRow(provinceList.size() + 4 + customerStatList.size() + 4 + customerTypeList.size() + 4);
                //创建标题
                for (int i = 0; i < sheet1OtherCustomerType.length; i++) {
                    sheet1Cell6 = sheet1Row4.createCell(i);
                    sheet1Cell6.setCellValue(sheet1OtherCustomerType[i]);
                    sheet1Cell6.setCellStyle(titleStyle8);
                }
                countValue = serviceResponseVo.getServiceReport().getCountPerson();
//                for (int i = 0; i < otherCustomerTypeList.size(); i++) {
//                    countValue += Integer.valueOf(otherCustomerTypeList.get(i)[1]);
//                }
                for (int i = 0; i < otherCustomerTypeList.size(); i++) {
                    otherCustomerTypeList.get(i)[2] = doubleGeTowDecimal(Double.valueOf(otherCustomerTypeList.get(i)[1]) / countValue);
                }
                strings = new String[3];
                strings[0] = "有效报告数";
                strings[1] = String.valueOf(countValue);
                strings[2] = "1";
                otherCustomerTypeList.add(strings);

                XSSFCell sheet1Cell7 = null;
                for (int i = 0; i < otherCustomerTypeList.size(); i++) {
                    sheet1Row4 = sheet1.createRow(i + provinceList.size() + 5 + customerStatList.size() + 4 + customerTypeList.size() + 4);
                    for (int j = 0; j < otherCustomerTypeList.get(i).length; j++) {
                        sheet1Cell7 = sheet1Row4.createCell(j);
                        //将内容按顺序赋给对应的列对象
                        Double doubleValue = 0D;
                        if (j == otherCustomerTypeList.get(i).length - 1) {
                            if (i % 2 == 0) {
                                if (j == 2) {
                                    doubleValue = Double.valueOf(otherCustomerTypeList.get(i)[j]);
                                    titleStyle222.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                                sheet1Cell7.setCellStyle(titleStyle222);
                            } else {
                                if (j == 2) {
                                    if (StringUtils.isNotEmpty(otherCustomerTypeList.get(i)[j])) {
                                        doubleValue = Double.valueOf(otherCustomerTypeList.get(i)[j]);
                                        titleStyle555.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                    }
                                }
                                sheet1Cell7.setCellStyle(titleStyle555);
                            }
                            try {
                                if (j == 2) {
                                    sheet1Cell7.setCellValue(doubleValue);
                                } else {
                                    sheet1Cell7.setCellValue(Long.valueOf(otherCustomerTypeList.get(i)[j]));
                                }
                            } catch (Exception e) {
                                sheet1Cell7.setCellValue(otherCustomerTypeList.get(i)[j]);
                            }

                        } else {
                            if (i % 2 == 0) {
                                if (j == 2) {
                                    doubleValue = Double.valueOf(otherCustomerTypeList.get(i)[j]);
                                    titleStyle333.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                                sheet1Cell7.setCellStyle(titleStyle333);
                            } else {
                                if (j == 2) {
                                    if (StringUtils.isNotEmpty(otherCustomerTypeList.get(i)[j])) {
                                        doubleValue = Double.valueOf(otherCustomerTypeList.get(i)[j]);
                                        titleStyle444.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                    }
                                }
                                sheet1Cell7.setCellStyle(titleStyle444);
                            }
                            try {
                                if (j == 2) {
                                    sheet1Cell7.setCellValue(doubleValue);
                                } else {
                                    sheet1Cell7.setCellValue(Long.valueOf(otherCustomerTypeList.get(i)[j]));
                                }
                            } catch (Exception e) {
                                sheet1Cell7.setCellValue(otherCustomerTypeList.get(i)[j]);
                            }
                        }
                    }
                }
            }

            // 钣喷维修流程中面对最大问题
            XSSFColor myColor9 = new XSSFColor(new java.awt.Color(155, 187, 89));
            XSSFCellStyle titleStyle9 = workbook.createCellStyle();
            titleStyle9.setFillForegroundColor(myColor9);
            titleStyle9.setAlignment(HorizontalAlignment.CENTER);
            titleStyle9.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle9.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle9.setFont(font);
            titleStyle9.setBorderLeft(BorderStyle.THIN);
            titleStyle9.setLeftBorderColor(myColor9);
            titleStyle9.setBorderRight(BorderStyle.THIN);
            titleStyle9.setRightBorderColor(myColor9);
            titleStyle9.setBorderTop(BorderStyle.THIN);
            titleStyle9.setTopBorderColor(myColor9);

            XSSFColor myColor10 = new XSSFColor(new java.awt.Color(216, 228, 188));
            XSSFCellStyle titleStyle2222 = workbook.createCellStyle();
            titleStyle2222.setAlignment(HorizontalAlignment.CENTER);
            titleStyle2222.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle2222.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle2222.setFillForegroundColor(myColor10);
            titleStyle2222.setBorderTop(BorderStyle.THIN);
            titleStyle2222.setBorderBottom(BorderStyle.THIN);
            titleStyle2222.setBorderRight(BorderStyle.THIN);
            titleStyle2222.setTopBorderColor(myColor10);
            titleStyle2222.setFont(font2);


            XSSFCellStyle titleStyle3333 = workbook.createCellStyle();
            titleStyle3333.setAlignment(HorizontalAlignment.CENTER);
            titleStyle3333.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle3333.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle3333.setFillForegroundColor(myColor10);
            titleStyle3333.setBorderTop(BorderStyle.THIN);
            titleStyle3333.setBorderBottom(BorderStyle.THIN);
            titleStyle3333.setBorderRight(BorderStyle.NONE);
            titleStyle3333.setTopBorderColor(myColor10);
            titleStyle3333.setFont(font2);

            XSSFColor myColor11 = new XSSFColor(new java.awt.Color(235, 241, 222));
            XSSFCellStyle titleStyle4444 = workbook.createCellStyle();
            titleStyle4444.setAlignment(HorizontalAlignment.CENTER);
            titleStyle4444.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle4444.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle4444.setFillForegroundColor(myColor11);
            titleStyle4444.setBorderTop(BorderStyle.THIN);
            titleStyle4444.setBorderBottom(BorderStyle.THIN);
            titleStyle4444.setBorderRight(BorderStyle.NONE);
            titleStyle4444.setTopBorderColor(myColor11);
            titleStyle4444.setFont(font2);

            XSSFCellStyle titleStyle5555 = workbook.createCellStyle();
            titleStyle5555.setAlignment(HorizontalAlignment.CENTER);
            titleStyle5555.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle5555.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle5555.setFillForegroundColor(myColor11);
            titleStyle5555.setBorderTop(BorderStyle.THIN);
            titleStyle5555.setBorderBottom(BorderStyle.THIN);
            titleStyle5555.setBorderRight(BorderStyle.THIN);
            titleStyle5555.setTopBorderColor(myColor11);
            titleStyle5555.setFont(font2);

            List<String[]> maintainBigProblemList = serviceResponseVo.getServiceReport().getMaintainBigProblem();
            XSSFCell sheet1Cell7 = null;
            String[] sheet1maintainBigProblem = {"钣喷维修流程中面对最大问题", "站点数量", "比例"};
            int otherCustomerTypeCount = 0;
            if (otherCustomerTypeList != null) {
                otherCustomerTypeCount = otherCustomerTypeList.size() + 4;
            }
            XSSFRow sheet1Row5 = sheet1.createRow(provinceList.size() + 4 + customerStatList.size() + 4 + customerTypeList.size() + 4 + otherCustomerTypeCount);
            //创建标题
            for (int i = 0; i < sheet1maintainBigProblem.length; i++) {
                sheet1Cell7 = sheet1Row5.createCell(i);
                sheet1Cell7.setCellValue(sheet1maintainBigProblem[i]);
                sheet1Cell7.setCellStyle(titleStyle9);
            }
            countValue = serviceResponseVo.getServiceReport().getCountPerson();
//            for (int i = 0; i < provinceList.size(); i++) {
//                countValue += Integer.valueOf(provinceList.get(i)[1]);
//            }
            for (int i = 0; i < maintainBigProblemList.size(); i++) {
                maintainBigProblemList.get(i)[2] = doubleGeTowDecimal(Double.valueOf(maintainBigProblemList.get(i)[1]) / countValue);
            }
            strings = new String[3];
            strings[0] = "有效报告数";
            strings[1] = String.valueOf(countValue);
            strings[2] = "1";
            maintainBigProblemList.add(strings);

            XSSFCell sheet1Cell8 = null;
            for (int i = 0; i < maintainBigProblemList.size(); i++) {
                sheet1Row5 = sheet1.createRow(i + provinceList.size() + 5 + customerStatList.size() + 4 + customerTypeList.size() + 4 + otherCustomerTypeCount);
                for (int j = 0; j < maintainBigProblemList.get(i).length; j++) {
                    sheet1Cell8 = sheet1Row5.createCell(j);
                    //将内容按顺序赋给对应的列对象
                    Double doubleValue = 0D;
                    if (j == maintainBigProblemList.get(i).length - 1) {
                        if (i % 2 == 0) {
                            if (j == 2) {
                                doubleValue = Double.valueOf(maintainBigProblemList.get(i)[j]);
                                titleStyle2222.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                            }
                            sheet1Cell8.setCellStyle(titleStyle2222);
                        } else {
                            if (j == 2) {
                                if (StringUtils.isNotEmpty(maintainBigProblemList.get(i)[j])) {
                                    doubleValue = Double.valueOf(maintainBigProblemList.get(i)[j]);
                                    titleStyle5555.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                            }
                            sheet1Cell8.setCellStyle(titleStyle5555);
                        }
                        try {
                            if (j == 2) {
                                sheet1Cell8.setCellValue(doubleValue);
                            } else {
                                sheet1Cell8.setCellValue(Long.valueOf(maintainBigProblemList.get(i)[j]));
                            }
                        } catch (Exception e) {
                            sheet1Cell8.setCellValue(maintainBigProblemList.get(i)[j]);
                        }

                    } else {
                        if (i % 2 == 0) {
                            if (j == 2) {
                                doubleValue = Double.valueOf(maintainBigProblemList.get(i)[j]);
                                titleStyle3333.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                            }
                            sheet1Cell8.setCellStyle(titleStyle3333);
                        } else {
                            if (j == 2) {
                                if (StringUtils.isNotEmpty(maintainBigProblemList.get(i)[j])) {
                                    doubleValue = Double.valueOf(maintainBigProblemList.get(i)[j]);
                                    titleStyle4444.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                            }
                            sheet1Cell8.setCellStyle(titleStyle4444);
                        }
                        try {
                            if (j == 2) {
                                sheet1Cell8.setCellValue(doubleValue);
                            } else {
                                sheet1Cell8.setCellValue(Long.valueOf(maintainBigProblemList.get(i)[j]));
                            }
                        } catch (Exception e) {
                            sheet1Cell8.setCellValue(maintainBigProblemList.get(i)[j]);
                        }
                    }
                }
            }

            // 其他钣喷维修流程中面对最大问题
            List<String[]> otherMaintainBigProblemList = serviceResponseVo.getServiceReport().getOtherMaintainBigProblem();
            if (otherMaintainBigProblemList != null && otherMaintainBigProblemList.size() > 0) {
                XSSFCell sheet1Cell9 = null;
                String[] sheet1OthermaintainBigProblem = {"其他钣喷维修流程中面对最大问题", "站点数量", "比例"};
                XSSFRow sheet1Row6 = sheet1.createRow(provinceList.size() + 4 + customerStatList.size() + 4 + customerTypeList.size() + 4 + otherCustomerTypeCount + maintainBigProblemList.size() + 4);
                //创建标题
                for (int i = 0; i < sheet1OthermaintainBigProblem.length; i++) {
                    sheet1Cell9 = sheet1Row6.createCell(i);
                    sheet1Cell9.setCellValue(sheet1OthermaintainBigProblem[i]);
                    sheet1Cell9.setCellStyle(titleStyle9);
                }
                countValue = serviceResponseVo.getServiceReport().getCountPerson();
//            for (int i = 0; i < provinceList.size(); i++) {
//                countValue += Integer.valueOf(provinceList.get(i)[1]);
//            }
                for (int i = 0; i < otherMaintainBigProblemList.size(); i++) {
                    otherMaintainBigProblemList.get(i)[2] = doubleGeTowDecimal(Double.valueOf(otherMaintainBigProblemList.get(i)[1]) / countValue);
                }
                strings = new String[3];
                strings[0] = "有效报告数";
                strings[1] = String.valueOf(countValue);
                strings[2] = "1";
                otherMaintainBigProblemList.add(strings);
                XSSFCell sheet1Cell10 = null;
                for (int i = 0; i < otherMaintainBigProblemList.size(); i++) {
                    sheet1Row6 = sheet1.createRow(i + provinceList.size() + 5 + customerStatList.size() + 4 + customerTypeList.size() + 4 + otherCustomerTypeCount + maintainBigProblemList.size() + 4);
                    for (int j = 0; j < otherMaintainBigProblemList.get(i).length; j++) {
                        sheet1Cell10 = sheet1Row6.createCell(j);
                        //将内容按顺序赋给对应的列对象
                        Double doubleValue = 0D;
                        if (j == otherMaintainBigProblemList.get(i).length - 1) {
                            if (i % 2 == 0) {
                                if (j == 2) {
                                    doubleValue = Double.valueOf(otherMaintainBigProblemList.get(i)[j]);
                                    titleStyle2222.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                                sheet1Cell10.setCellStyle(titleStyle2222);
                            } else {
                                if (j == 2) {
                                    if (StringUtils.isNotEmpty(otherMaintainBigProblemList.get(i)[j])) {
                                        doubleValue = Double.valueOf(otherMaintainBigProblemList.get(i)[j]);
                                        titleStyle5555.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                    }
                                }
                                sheet1Cell10.setCellStyle(titleStyle5555);
                            }
                            try {
                                if (j == 2) {
                                    sheet1Cell10.setCellValue(doubleValue);
                                } else {
                                    sheet1Cell10.setCellValue(Long.valueOf(otherMaintainBigProblemList.get(i)[j]));
                                }
                            } catch (Exception e) {
                                sheet1Cell10.setCellValue(otherMaintainBigProblemList.get(i)[j]);
                            }

                        } else {
                            if (i % 2 == 0) {
                                if (j == 2) {
                                    doubleValue = Double.valueOf(otherMaintainBigProblemList.get(i)[j]);
                                    titleStyle3333.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                                sheet1Cell10.setCellStyle(titleStyle3333);
                            } else {
                                if (j == 2) {
                                    if (StringUtils.isNotEmpty(otherMaintainBigProblemList.get(i)[j])) {
                                        doubleValue = Double.valueOf(otherMaintainBigProblemList.get(i)[j]);
                                        titleStyle4444.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                    }
                                }
                                sheet1Cell10.setCellStyle(titleStyle4444);
                            }
                            try {
                                if (j == 2) {
                                    sheet1Cell10.setCellValue(doubleValue);
                                } else {
                                    sheet1Cell10.setCellValue(Long.valueOf(otherMaintainBigProblemList.get(i)[j]));
                                }
                            } catch (Exception e) {
                                sheet1Cell10.setCellValue(otherMaintainBigProblemList.get(i)[j]);
                            }
                        }
                    }
                }
            }

            // 维修站类型
            XSSFColor myColor12 = new XSSFColor(new java.awt.Color(128, 100, 162));
            XSSFCellStyle titleStyle10 = workbook.createCellStyle();
            titleStyle10.setFillForegroundColor(myColor12);
            titleStyle10.setAlignment(HorizontalAlignment.CENTER);
            titleStyle10.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle10.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle10.setFont(font);
            titleStyle10.setBorderLeft(BorderStyle.THIN);
            titleStyle10.setLeftBorderColor(myColor12);
            titleStyle10.setBorderRight(BorderStyle.THIN);
            titleStyle10.setRightBorderColor(myColor12);
            titleStyle10.setBorderTop(BorderStyle.THIN);
            titleStyle10.setTopBorderColor(myColor12);

            XSSFColor myColor13 = new XSSFColor(new java.awt.Color(204, 192, 218));
            XSSFCellStyle titleStyle22222 = workbook.createCellStyle();
            titleStyle22222.setAlignment(HorizontalAlignment.CENTER);
            titleStyle22222.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle22222.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle22222.setFillForegroundColor(myColor13);
            titleStyle22222.setBorderTop(BorderStyle.THIN);
            titleStyle22222.setBorderBottom(BorderStyle.THIN);
            titleStyle22222.setBorderRight(BorderStyle.THIN);
            titleStyle22222.setTopBorderColor(myColor13);
            titleStyle22222.setFont(font2);


            XSSFCellStyle titleStyle33333 = workbook.createCellStyle();
            titleStyle33333.setAlignment(HorizontalAlignment.CENTER);
            titleStyle33333.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle33333.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle33333.setFillForegroundColor(myColor13);
            titleStyle33333.setBorderTop(BorderStyle.THIN);
            titleStyle33333.setBorderBottom(BorderStyle.THIN);
            titleStyle33333.setBorderRight(BorderStyle.NONE);
            titleStyle33333.setTopBorderColor(myColor13);
            titleStyle33333.setFont(font2);

            XSSFColor myColor14 = new XSSFColor(new java.awt.Color(228, 223, 236));
            XSSFCellStyle titleStyle44444 = workbook.createCellStyle();
            titleStyle44444.setAlignment(HorizontalAlignment.CENTER);
            titleStyle44444.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle44444.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle44444.setFillForegroundColor(myColor14);
            titleStyle44444.setBorderTop(BorderStyle.THIN);
            titleStyle44444.setBorderBottom(BorderStyle.THIN);
            titleStyle44444.setBorderRight(BorderStyle.NONE);
            titleStyle44444.setTopBorderColor(myColor14);
            titleStyle44444.setFont(font2);

            XSSFCellStyle titleStyle55555 = workbook.createCellStyle();
            titleStyle55555.setAlignment(HorizontalAlignment.CENTER);
            titleStyle55555.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle55555.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle55555.setFillForegroundColor(myColor14);
            titleStyle55555.setBorderTop(BorderStyle.THIN);
            titleStyle55555.setBorderBottom(BorderStyle.THIN);
            titleStyle55555.setBorderRight(BorderStyle.THIN);
            titleStyle55555.setTopBorderColor(myColor14);
            titleStyle55555.setFont(font2);

            List<String[]> stationTypeList = new ArrayList<>();
            String[] type = new String[3];
            Integer BPC = serviceResponseVo.getServiceReport().getVstarServiceReport_serviceType().getBPC();
            type[0] = "钣喷中心(BPC)";
            type[1] = BPC.toString();
            type[2] = "";
            stationTypeList.add(type);
            Integer FBS = serviceResponseVo.getServiceReport().getVstarServiceReport_serviceType().getFBS();
            type = new String[3];
            type[0] = "连锁维修站(FBS)";
            type[1] = FBS.toString();
            type[2] = "";
            stationTypeList.add(type);
            Integer FourS = serviceResponseVo.getServiceReport().getVstarServiceReport_serviceType().getFourS();
            type = new String[3];
            type[0] = "4S店(4S)";
            type[1] = FourS.toString();
            type[2] = "";
            stationTypeList.add(type);
            Integer IBS = serviceResponseVo.getServiceReport().getVstarServiceReport_serviceType().getIBS();
            type = new String[3];
            type[0] = "独立维修站(IBS)";
            type[1] = IBS.toString();
            type[2] = "";
            stationTypeList.add(type);
            Integer DEALERS = serviceResponseVo.getServiceReport().getVstarServiceReport_serviceType().getDEALERS();
            type = new String[3];
            type[0] = "经销商";
            type[1] = DEALERS.toString();
            type[2] = "";
            stationTypeList.add(type);


            int otherMainList = 0;
            if (otherMaintainBigProblemList != null) {
                otherMainList = otherMaintainBigProblemList.size() + 4;
            }
            XSSFCell sheet1Cell11 = null;
            String[] sheet1StationType = {"维修站类型", "站点数量", "比例"};
            XSSFRow sheet1Row7 = sheet1.createRow(provinceList.size() + 4 + customerStatList.size() + 4 + customerTypeList.size() + 4 + otherCustomerTypeCount + maintainBigProblemList.size() + 4 + otherMainList);
            //创建标题
            for (int i = 0; i < sheet1StationType.length; i++) {
                sheet1Cell11 = sheet1Row7.createCell(i);
                sheet1Cell11.setCellValue(sheet1StationType[i]);
                sheet1Cell11.setCellStyle(titleStyle10);
            }
            countValue = serviceResponseVo.getServiceReport().getCountPerson();
//            for (int i = 0; i < provinceList.size(); i++) {
//                countValue += Integer.valueOf(provinceList.get(i)[1]);
//            }
            for (int i = 0; i < stationTypeList.size(); i++) {
                stationTypeList.get(i)[2] = doubleGeTowDecimal(Double.valueOf(stationTypeList.get(i)[1]) / countValue);
            }
            strings = new String[3];
            strings[0] = "有效报告数";
            strings[1] = String.valueOf(countValue);
            strings[2] = "1";
            stationTypeList.add(strings);

            XSSFCell sheet1Cell12 = null;
            for (int i = 0; i < stationTypeList.size(); i++) {
                sheet1Row7 = sheet1.createRow(i + provinceList.size() + 5 + customerStatList.size() + 4 + customerTypeList.size() + 4 + otherCustomerTypeCount + maintainBigProblemList.size() + 4 + otherMainList);
                for (int j = 0; j < stationTypeList.get(i).length; j++) {
                    sheet1Cell12 = sheet1Row7.createCell(j);
                    //将内容按顺序赋给对应的列对象
                    Double doubleValue = 0D;
                    if (j == stationTypeList.get(i).length - 1) {
                        if (i % 2 == 0) {
                            if (j == 2) {
                                doubleValue = Double.valueOf(stationTypeList.get(i)[j]);
                                titleStyle22222.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                            }
                            sheet1Cell12.setCellStyle(titleStyle22222);
                        } else {
                            if (j == 2) {
                                if (StringUtils.isNotEmpty(stationTypeList.get(i)[j])) {
                                    doubleValue = Double.valueOf(stationTypeList.get(i)[j]);
                                    titleStyle55555.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                            }
                            sheet1Cell12.setCellStyle(titleStyle55555);
                        }
                        try {
                            if (j == 2) {
                                sheet1Cell12.setCellValue(doubleValue);
                            } else {
                                sheet1Cell12.setCellValue(Long.valueOf(stationTypeList.get(i)[j]));
                            }
                        } catch (Exception e) {
                            sheet1Cell12.setCellValue(stationTypeList.get(i)[j]);
                        }

                    } else {
                        if (i % 2 == 0) {
                            if (j == 2) {
                                doubleValue = Double.valueOf(stationTypeList.get(i)[j]);
                                titleStyle33333.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                            }
                            sheet1Cell12.setCellStyle(titleStyle33333);
                        } else {
                            if (j == 2) {
                                if (StringUtils.isNotEmpty(stationTypeList.get(i)[j])) {
                                    doubleValue = Double.valueOf(stationTypeList.get(i)[j]);
                                    titleStyle44444.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                            }
                            sheet1Cell12.setCellStyle(titleStyle44444);
                        }
                        try {
                            if (j == 2) {
                                sheet1Cell12.setCellValue(doubleValue);
                            } else {
                                sheet1Cell12.setCellValue(Long.valueOf(stationTypeList.get(i)[j]));
                            }
                        } catch (Exception e) {
                            sheet1Cell12.setCellValue(stationTypeList.get(i)[j]);
                        }
                    }
                }
            }

            //维修站具体品牌
            XSSFColor myColor15 = new XSSFColor(new java.awt.Color(79, 129, 189));
            XSSFCellStyle titleStyle11 = workbook.createCellStyle();
            titleStyle11.setFillForegroundColor(myColor15);
            titleStyle11.setAlignment(HorizontalAlignment.CENTER);
            titleStyle11.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle11.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle11.setFont(font);
            titleStyle11.setBorderLeft(BorderStyle.THIN);
            titleStyle11.setLeftBorderColor(myColor15);
            titleStyle11.setBorderRight(BorderStyle.THIN);
            titleStyle11.setRightBorderColor(myColor15);
            titleStyle11.setBorderTop(BorderStyle.THIN);
            titleStyle11.setTopBorderColor(myColor15);

            XSSFColor myColor16 = new XSSFColor(new java.awt.Color(184, 204, 228));
            XSSFCellStyle titleStyle222222 = workbook.createCellStyle();
            titleStyle222222.setAlignment(HorizontalAlignment.CENTER);
            titleStyle222222.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle222222.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle222222.setFillForegroundColor(myColor16);
            titleStyle222222.setBorderTop(BorderStyle.THIN);
            titleStyle222222.setBorderBottom(BorderStyle.THIN);
            titleStyle222222.setBorderRight(BorderStyle.THIN);
            titleStyle222222.setTopBorderColor(myColor16);
            titleStyle222222.setFont(font2);

            XSSFCellStyle titleStyle333333 = workbook.createCellStyle();
            titleStyle333333.setAlignment(HorizontalAlignment.CENTER);
            titleStyle333333.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle333333.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle333333.setFillForegroundColor(myColor16);
            titleStyle333333.setBorderTop(BorderStyle.THIN);
            titleStyle333333.setBorderBottom(BorderStyle.THIN);
            titleStyle333333.setBorderRight(BorderStyle.NONE);
            titleStyle333333.setTopBorderColor(myColor16);
            titleStyle333333.setFont(font2);

            XSSFColor myColor17 = new XSSFColor(new java.awt.Color(220, 230, 241));
            XSSFCellStyle titleStyle444444 = workbook.createCellStyle();
            titleStyle444444.setAlignment(HorizontalAlignment.CENTER);
            titleStyle444444.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle444444.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle444444.setFillForegroundColor(myColor17);
            titleStyle444444.setBorderTop(BorderStyle.THIN);
            titleStyle444444.setBorderBottom(BorderStyle.THIN);
            titleStyle444444.setBorderRight(BorderStyle.NONE);
            titleStyle444444.setTopBorderColor(myColor17);
            titleStyle444444.setFont(font2);

            XSSFCellStyle titleStyle555555 = workbook.createCellStyle();
            titleStyle555555.setAlignment(HorizontalAlignment.CENTER);
            titleStyle555555.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle555555.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle555555.setFillForegroundColor(myColor17);
            titleStyle555555.setBorderTop(BorderStyle.THIN);
            titleStyle555555.setBorderBottom(BorderStyle.THIN);
            titleStyle555555.setBorderRight(BorderStyle.THIN);
            titleStyle555555.setTopBorderColor(myColor17);
            titleStyle555555.setFont(font2);

            List<String[]> brandArrayList = serviceResponseVo.getServiceReport().getVstarServiceReport_brand().getBrandArray();
            XSSFCell sheet1Cell15 = null;
            String[] brandTitile = {"维修站具体品牌", "站点数量", "比例"};
            XSSFRow sheet1Row8 = sheet1.createRow(provinceList.size() + 5 + customerStatList.size() + 4 + customerTypeList.size() + 4 +
                    otherCustomerTypeCount + maintainBigProblemList.size() + 4 + otherMainList + stationTypeList.size() + 3);
            //创建标题
            for (int i = 0; i < brandTitile.length; i++) {
                sheet1Cell15 = sheet1Row8.createCell(i);
                sheet1Cell15.setCellValue(brandTitile[i]);
                sheet1Cell15.setCellStyle(titleStyle11);
            }
            countValue = serviceResponseVo.getServiceReport().getCountPerson();
//            for (int i = 0; i < provinceList.size(); i++) {
//                countValue += Integer.valueOf(provinceList.get(i)[1]);
//            }
            for (int i = 0; i < brandArrayList.size(); i++) {
                brandArrayList.get(i)[2] = doubleGeTowDecimal(Double.valueOf(brandArrayList.get(i)[1]) / countValue);
            }
            strings = new String[3];
            strings[0] = "有效报告数";
            strings[1] = String.valueOf(countValue);
            strings[2] = "1";
            brandArrayList.add(strings);

            XSSFCell sheet1Cell14 = null;
            for (int i = 0; i < brandArrayList.size(); i++) {
                sheet1Row8 = sheet1.createRow(i + provinceList.size() + 5 + customerStatList.size() + 4 + customerTypeList.size() + 4 +
                        otherCustomerTypeCount + maintainBigProblemList.size() + 4 + otherMainList + stationTypeList.size() + 4);
                for (int j = 0; j < brandArrayList.get(i).length; j++) {
                    sheet1Cell14 = sheet1Row8.createCell(j);
                    //将内容按顺序赋给对应的列对象
                    Double doubleValue = 0D;
                    if (j == brandArrayList.get(i).length - 1) {
                        if (i % 2 == 0) {
                            if (j == 2) {
                                doubleValue = Double.valueOf(brandArrayList.get(i)[j]);
                                titleStyle222222.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                            }
                            sheet1Cell14.setCellStyle(titleStyle222222);
                        } else {
                            if (j == 2) {
                                if (StringUtils.isNotEmpty(brandArrayList.get(i)[j])) {
                                    doubleValue = Double.valueOf(brandArrayList.get(i)[j]);
                                    titleStyle555555.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                            }
                            sheet1Cell14.setCellStyle(titleStyle555555);
                        }
                        try {
                            if (j == 2) {
                                sheet1Cell14.setCellValue(doubleValue);
                            } else {
                                sheet1Cell14.setCellValue(Long.valueOf(brandArrayList.get(i)[j]));
                            }
                        } catch (Exception e) {
                            sheet1Cell14.setCellValue(brandArrayList.get(i)[j]);
                        }

                    } else {
                        if (i % 2 == 0) {
                            if (j == 2) {
                                doubleValue = Double.valueOf(brandArrayList.get(i)[j]);
                                titleStyle333333.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                            }
                            sheet1Cell14.setCellStyle(titleStyle333333);
                        } else {
                            if (j == 2) {
                                if (StringUtils.isNotEmpty(brandArrayList.get(i)[j])) {
                                    doubleValue = Double.valueOf(brandArrayList.get(i)[j]);
                                    titleStyle444444.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                            }
                            sheet1Cell14.setCellStyle(titleStyle444444);
                        }
                        try {
                            if (j == 2) {
                                sheet1Cell14.setCellValue(doubleValue);
                            } else {
                                sheet1Cell14.setCellValue(Long.valueOf(brandArrayList.get(i)[j]));
                            }
                        } catch (Exception e) {
                            sheet1Cell14.setCellValue(brandArrayList.get(i)[j]);
                        }
                    }
                }
            }

            //其他具体品牌
            List<String[]> otherBrandArrayList = serviceResponseVo.getServiceReport().getVstarServiceReport_brand().getOtherBrandList();
            XSSFCell sheet1Cell13 = null;
            String[] sheet1StationOtherBrand = {"其他维修站品牌", "站点数量", "比例"};
            XSSFRow sheet1Row9 = sheet1.createRow(provinceList.size() + 5 + customerStatList.size() + 4 + customerTypeList.size() + 4 +
                    otherCustomerTypeCount + maintainBigProblemList.size() + 4 + otherMainList + stationTypeList.size() + 4 + brandArrayList.size() + 3);
            //创建标题
            for (int i = 0; i < sheet1StationOtherBrand.length; i++) {
                sheet1Cell13 = sheet1Row9.createCell(i);
                sheet1Cell13.setCellValue(sheet1StationOtherBrand[i]);
                sheet1Cell13.setCellStyle(titleStyle11);
            }
            countValue = serviceResponseVo.getServiceReport().getCountPerson();
//            for (int i = 0; i < provinceList.size(); i++) {
//                countValue += Integer.valueOf(provinceList.get(i)[1]);
//            }
            for (int i = 0; i < otherBrandArrayList.size(); i++) {
                otherBrandArrayList.get(i)[2] = doubleGeTowDecimal(Double.valueOf(otherBrandArrayList.get(i)[1]) / countValue);
            }
            strings = new String[3];
            strings[0] = "有效报告数";
            strings[1] = String.valueOf(countValue);
            strings[2] = "1";
            otherBrandArrayList.add(strings);

            XSSFCell sheet1Cell16 = null;
            for (int i = 0; i < otherBrandArrayList.size(); i++) {
                sheet1Row8 = sheet1.createRow(i + provinceList.size() + 5 + customerStatList.size() + 4 + customerTypeList.size() + 4 +
                        otherCustomerTypeCount + maintainBigProblemList.size() + 4 + otherMainList + stationTypeList.size() + 4 + brandArrayList.size() + 4);
                for (int j = 0; j < otherBrandArrayList.get(i).length; j++) {
                    sheet1Cell16 = sheet1Row8.createCell(j);
                    //将内容按顺序赋给对应的列对象
                    Double doubleValue = 0D;
                    if (j == otherBrandArrayList.get(i).length - 1) {
                        if (i % 2 == 0) {
                            if (j == 2) {
                                doubleValue = Double.valueOf(otherBrandArrayList.get(i)[j]);
                                titleStyle222222.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                            }
                            sheet1Cell16.setCellStyle(titleStyle222222);
                        } else {
                            if (j == 2) {
                                if (StringUtils.isNotEmpty(otherBrandArrayList.get(i)[j])) {
                                    doubleValue = Double.valueOf(otherBrandArrayList.get(i)[j]);
                                    titleStyle555555.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                            }
                            sheet1Cell16.setCellStyle(titleStyle555555);
                        }
                        try {
                            if (j == 2) {
                                sheet1Cell16.setCellValue(doubleValue);
                            } else {
                                sheet1Cell16.setCellValue(Long.valueOf(otherBrandArrayList.get(i)[j]));
                            }
                        } catch (Exception e) {
                            sheet1Cell16.setCellValue(otherBrandArrayList.get(i)[j]);
                        }

                    } else {
                        if (i % 2 == 0) {
                            if (j == 2) {
                                doubleValue = Double.valueOf(otherBrandArrayList.get(i)[j]);
                                titleStyle333333.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                            }
                            sheet1Cell16.setCellStyle(titleStyle333333);
                        } else {
                            if (j == 2) {
                                if (StringUtils.isNotEmpty(otherBrandArrayList.get(i)[j])) {
                                    doubleValue = Double.valueOf(otherBrandArrayList.get(i)[j]);
                                    titleStyle444444.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                            }
                            sheet1Cell16.setCellStyle(titleStyle444444);
                        }
                        try {
                            if (j == 2) {
                                sheet1Cell16.setCellValue(doubleValue);
                            } else {
                                sheet1Cell16.setCellValue(Long.valueOf(otherBrandArrayList.get(i)[j]));
                            }
                        } catch (Exception e) {
                            sheet1Cell16.setCellValue(otherBrandArrayList.get(i)[j]);
                        }
                    }
                }
            }

            //维修站主要诉求
            XSSFColor myColor18 = new XSSFColor(new java.awt.Color(75, 172, 198));
            XSSFCellStyle titleStyle12 = workbook.createCellStyle();
            titleStyle12.setFillForegroundColor(myColor18);
            titleStyle12.setAlignment(HorizontalAlignment.CENTER);
            titleStyle12.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle12.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle12.setFont(font);
            titleStyle12.setBorderLeft(BorderStyle.THIN);
            titleStyle12.setLeftBorderColor(myColor18);
            titleStyle12.setBorderRight(BorderStyle.THIN);
            titleStyle12.setRightBorderColor(myColor18);
            titleStyle12.setBorderTop(BorderStyle.THIN);
            titleStyle12.setTopBorderColor(myColor18);

            XSSFColor myColor19 = new XSSFColor(new java.awt.Color(183, 222, 232));
            XSSFCellStyle titleStyle2222222 = workbook.createCellStyle();
            titleStyle2222222.setAlignment(HorizontalAlignment.CENTER);
            titleStyle2222222.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle2222222.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle2222222.setFillForegroundColor(myColor19);
            titleStyle2222222.setBorderTop(BorderStyle.THIN);
            titleStyle2222222.setBorderBottom(BorderStyle.THIN);
            titleStyle2222222.setBorderRight(BorderStyle.THIN);
            titleStyle2222222.setTopBorderColor(myColor19);
            titleStyle2222222.setFont(font2);

            XSSFCellStyle titleStyle3333333 = workbook.createCellStyle();
            titleStyle3333333.setAlignment(HorizontalAlignment.CENTER);
            titleStyle3333333.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle3333333.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle3333333.setFillForegroundColor(myColor19);
            titleStyle3333333.setBorderTop(BorderStyle.THIN);
            titleStyle3333333.setBorderBottom(BorderStyle.THIN);
            titleStyle3333333.setBorderRight(BorderStyle.NONE);
            titleStyle3333333.setTopBorderColor(myColor19);
            titleStyle3333333.setFont(font2);

            XSSFColor myColor20 = new XSSFColor(new java.awt.Color(218, 238, 243));
            XSSFCellStyle titleStyle4444444 = workbook.createCellStyle();
            titleStyle4444444.setAlignment(HorizontalAlignment.CENTER);
            titleStyle4444444.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle4444444.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle4444444.setFillForegroundColor(myColor20);
            titleStyle4444444.setBorderTop(BorderStyle.THIN);
            titleStyle4444444.setBorderBottom(BorderStyle.THIN);
            titleStyle4444444.setBorderRight(BorderStyle.NONE);
            titleStyle4444444.setTopBorderColor(myColor20);
            titleStyle4444444.setFont(font2);

            XSSFCellStyle titleStyle5555555 = workbook.createCellStyle();
            titleStyle5555555.setAlignment(HorizontalAlignment.CENTER);
            titleStyle5555555.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle5555555.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle5555555.setFillForegroundColor(myColor20);
            titleStyle5555555.setBorderTop(BorderStyle.THIN);
            titleStyle5555555.setBorderBottom(BorderStyle.THIN);
            titleStyle5555555.setBorderRight(BorderStyle.THIN);
            titleStyle5555555.setTopBorderColor(myColor20);
            titleStyle5555555.setFont(font2);

            List<String[]> stationAppealList = serviceResponseVo.getServiceReport().getStationAppeal();
            XSSFCell sheet1Cell17 = null;
            String[] sheet1StationAppeal = {"维修站主要诉求", "站点数量", "比例"};
            XSSFRow sheet1Row10 = sheet1.createRow(provinceList.size() + 5 + customerStatList.size() + 4 + customerTypeList.size() + 4 +
                    otherCustomerTypeCount + maintainBigProblemList.size() + 4 + otherMainList + stationTypeList.size() + 4 + brandArrayList.size() +
                    3 + otherBrandArrayList.size() + 4);
            //创建标题
            for (int i = 0; i < sheet1StationAppeal.length; i++) {
                sheet1Cell17 = sheet1Row10.createCell(i);
                sheet1Cell17.setCellValue(sheet1StationAppeal[i]);
                sheet1Cell17.setCellStyle(titleStyle7);
            }
            countValue = serviceResponseVo.getServiceReport().getCountPerson();
//            for (int i = 0; i < provinceList.size(); i++) {
//                countValue += Integer.valueOf(provinceList.get(i)[1]);
//            }
            for (int i = 0; i < stationAppealList.size(); i++) {
                stationAppealList.get(i)[2] = doubleGeTowDecimal(Double.valueOf(stationAppealList.get(i)[1]) / countValue);
            }
            strings = new String[3];
            strings[0] = "有效报告数";
            strings[1] = String.valueOf(countValue);
            strings[2] = "1";
            stationAppealList.add(strings);

            XSSFCell sheet1Cell18 = null;
            for (int i = 0; i < stationAppealList.size(); i++) {
                sheet1Row10 = sheet1.createRow(i + provinceList.size() + 5 + customerStatList.size() + 4 + customerTypeList.size() + 4 +
                        otherCustomerTypeCount + maintainBigProblemList.size() + 4 + otherMainList + stationTypeList.size() + 4 + brandArrayList.size() + 4 +
                        otherBrandArrayList.size() + 4);
                for (int j = 0; j < stationAppealList.get(i).length; j++) {
                    sheet1Cell18 = sheet1Row10.createCell(j);
                    //将内容按顺序赋给对应的列对象
                    Double doubleValue = 0D;
                    if (j == stationAppealList.get(i).length - 1) {
                        if (i % 2 == 0) {
                            if (j == 2) {
                                doubleValue = Double.valueOf(stationAppealList.get(i)[j]);
                                titleStyle2222222.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                            }
                            sheet1Cell18.setCellStyle(titleStyle2222222);
                        } else {
                            if (j == 2) {
                                if (StringUtils.isNotEmpty(stationAppealList.get(i)[j])) {
                                    doubleValue = Double.valueOf(stationAppealList.get(i)[j]);
                                    titleStyle5555555.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                            }
                            sheet1Cell18.setCellStyle(titleStyle5555555);
                        }
                        try {
                            if (j == 2) {
                                sheet1Cell18.setCellValue(doubleValue);
                            } else {
                                sheet1Cell18.setCellValue(Long.valueOf(stationAppealList.get(i)[j]));
                            }
                        } catch (Exception e) {
                            sheet1Cell18.setCellValue(stationAppealList.get(i)[j]);
                        }

                    } else {
                        if (i % 2 == 0) {
                            if (j == 2) {
                                doubleValue = Double.valueOf(stationAppealList.get(i)[j]);
                                titleStyle3333333.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                            }
                            sheet1Cell18.setCellStyle(titleStyle3333333);
                        } else {
                            if (j == 2) {
                                if (StringUtils.isNotEmpty(stationAppealList.get(i)[j])) {
                                    doubleValue = Double.valueOf(stationAppealList.get(i)[j]);
                                    titleStyle4444444.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                            }
                            sheet1Cell18.setCellStyle(titleStyle4444444);
                        }
                        try {
                            if (j == 2) {
                                sheet1Cell18.setCellValue(doubleValue);
                            } else {
                                sheet1Cell18.setCellValue(Long.valueOf(stationAppealList.get(i)[j]));
                            }
                        } catch (Exception e) {
                            sheet1Cell18.setCellValue(stationAppealList.get(i)[j]);
                        }
                    }
                }
            }

            //油漆品牌
            XSSFColor myColor21 = new XSSFColor(new java.awt.Color(247, 150, 70));
            XSSFCellStyle titleStyle13 = workbook.createCellStyle();
            titleStyle13.setFillForegroundColor(myColor21);
            titleStyle13.setAlignment(HorizontalAlignment.CENTER);
            titleStyle13.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle13.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle13.setFont(font);
            titleStyle13.setBorderLeft(BorderStyle.THIN);
            titleStyle13.setLeftBorderColor(myColor21);
            titleStyle13.setBorderRight(BorderStyle.THIN);
            titleStyle13.setRightBorderColor(myColor21);
            titleStyle13.setBorderTop(BorderStyle.THIN);
            titleStyle13.setTopBorderColor(myColor21);

            XSSFColor myColor22 = new XSSFColor(new java.awt.Color(252, 213, 180));
            XSSFCellStyle titleStyle22222222 = workbook.createCellStyle();
            titleStyle22222222.setAlignment(HorizontalAlignment.CENTER);
            titleStyle22222222.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle22222222.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle22222222.setFillForegroundColor(myColor22);
            titleStyle22222222.setBorderTop(BorderStyle.THIN);
            titleStyle22222222.setBorderBottom(BorderStyle.THIN);
            titleStyle22222222.setBorderRight(BorderStyle.THIN);
            titleStyle22222222.setTopBorderColor(myColor22);
            titleStyle22222222.setFont(font2);

            XSSFCellStyle titleStyle33333333 = workbook.createCellStyle();
            titleStyle33333333.setAlignment(HorizontalAlignment.CENTER);
            titleStyle33333333.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle33333333.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle33333333.setFillForegroundColor(myColor22);
            titleStyle33333333.setBorderTop(BorderStyle.THIN);
            titleStyle33333333.setBorderBottom(BorderStyle.THIN);
            titleStyle33333333.setBorderRight(BorderStyle.NONE);
            titleStyle33333333.setTopBorderColor(myColor22);
            titleStyle33333333.setFont(font2);

            XSSFColor myColor23 = new XSSFColor(new java.awt.Color(253, 233, 217));
            XSSFCellStyle titleStyle44444444 = workbook.createCellStyle();
            titleStyle44444444.setAlignment(HorizontalAlignment.CENTER);
            titleStyle44444444.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle44444444.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle44444444.setFillForegroundColor(myColor23);
            titleStyle44444444.setBorderTop(BorderStyle.THIN);
            titleStyle44444444.setBorderBottom(BorderStyle.THIN);
            titleStyle44444444.setBorderRight(BorderStyle.NONE);
            titleStyle44444444.setTopBorderColor(myColor23);
            titleStyle44444444.setFont(font2);

            XSSFCellStyle titleStyle55555555 = workbook.createCellStyle();
            titleStyle55555555.setAlignment(HorizontalAlignment.CENTER);
            titleStyle55555555.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle55555555.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle55555555.setFillForegroundColor(myColor23);
            titleStyle55555555.setBorderTop(BorderStyle.THIN);
            titleStyle55555555.setBorderBottom(BorderStyle.THIN);
            titleStyle55555555.setBorderRight(BorderStyle.THIN);
            titleStyle55555555.setTopBorderColor(myColor23);
            titleStyle55555555.setFont(font2);

            List<String[]> paintBrandList = serviceResponseVo.getServiceReport().getPaintBrandList();
            XSSFCell sheet1Cell19 = null;
            String[] sheet1PaintBrandList = {"油漆品牌", "站点数量", "比例"};
            XSSFRow sheet1Row11 = sheet1.createRow(provinceList.size() + 5 + customerStatList.size() + 4 + customerTypeList.size() + 4 +
                    otherCustomerTypeCount + maintainBigProblemList.size() + 4 + otherMainList + stationTypeList.size() + 4 + brandArrayList.size() +
                    3 + otherBrandArrayList.size() + 4 + stationAppealList.size() + 4);
            //创建标题
            for (int i = 0; i < sheet1PaintBrandList.length; i++) {
                sheet1Cell19 = sheet1Row11.createCell(i);
                sheet1Cell19.setCellValue(sheet1PaintBrandList[i]);
                sheet1Cell19.setCellStyle(titleStyle13);
            }
            countValue = serviceResponseVo.getServiceReport().getCountPerson();
//            for (int i = 0; i < provinceList.size(); i++) {
//                countValue += Integer.valueOf(provinceList.get(i)[1]);
//            }
            for (int i = 0; i < paintBrandList.size(); i++) {
                paintBrandList.get(i)[2] = doubleGeTowDecimal(Double.valueOf(paintBrandList.get(i)[1]) / countValue);
            }
            strings = new String[3];
            strings[0] = "有效报告数";
            strings[1] = String.valueOf(countValue);
            strings[2] = "1";
            paintBrandList.add(strings);

            XSSFCell sheet1Cell20 = null;
            for (int i = 0; i < paintBrandList.size(); i++) {
                sheet1Row10 = sheet1.createRow(i + provinceList.size() + 5 + customerStatList.size() + 4 + customerTypeList.size() + 4 +
                        otherCustomerTypeCount + maintainBigProblemList.size() + 4 + otherMainList + stationTypeList.size() + 4 + brandArrayList.size() + 4 +
                        otherBrandArrayList.size() + 4 + stationAppealList.size() + 4);
                for (int j = 0; j < paintBrandList.get(i).length; j++) {
                    sheet1Cell20 = sheet1Row10.createCell(j);
                    //将内容按顺序赋给对应的列对象
                    Double doubleValue = 0D;
                    if (j == paintBrandList.get(i).length - 1) {
                        if (i % 2 == 0) {
                            if (j == 2) {
                                doubleValue = Double.valueOf(paintBrandList.get(i)[j]);
                                titleStyle22222222.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                            }
                            sheet1Cell20.setCellStyle(titleStyle22222222);
                        } else {
                            if (j == 2) {
                                if (StringUtils.isNotEmpty(paintBrandList.get(i)[j])) {
                                    doubleValue = Double.valueOf(paintBrandList.get(i)[j]);
                                    titleStyle55555555.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                            }
                            sheet1Cell20.setCellStyle(titleStyle55555555);
                        }
                        try {
                            if (j == 2) {
                                sheet1Cell20.setCellValue(doubleValue);
                            } else {
                                sheet1Cell20.setCellValue(Long.valueOf(paintBrandList.get(i)[j]));
                            }
                        } catch (Exception e) {
                            sheet1Cell20.setCellValue(paintBrandList.get(i)[j]);
                        }

                    } else {
                        if (i % 2 == 0) {
                            if (j == 2) {
                                doubleValue = Double.valueOf(paintBrandList.get(i)[j]);
                                titleStyle33333333.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                            }
                            sheet1Cell20.setCellStyle(titleStyle33333333);
                        } else {
                            if (j == 2) {
                                if (StringUtils.isNotEmpty(paintBrandList.get(i)[j])) {
                                    doubleValue = Double.valueOf(paintBrandList.get(i)[j]);
                                    titleStyle44444444.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                            }
                            sheet1Cell20.setCellStyle(titleStyle44444444);
                        }
                        try {
                            if (j == 2) {
                                sheet1Cell20.setCellValue(doubleValue);
                            } else {
                                sheet1Cell20.setCellValue(Long.valueOf(paintBrandList.get(i)[j]));
                            }
                        } catch (Exception e) {
                            sheet1Cell20.setCellValue(paintBrandList.get(i)[j]);
                        }
                    }
                }
            }

            List<String[]> otherPaintBrandList = serviceResponseVo.getServiceReport().getOtherPaintBrandList();
            int otherPaintBrandCount = 0;
            if (otherPaintBrandList != null && otherPaintBrandList.size() > 0) {
                otherPaintBrandCount = otherPaintBrandList.size() + 4;
                XSSFCell sheet1Cell21 = null;
                String[] sheet1OtherPaintBrandList = {"其他油漆品牌", "站点数量", "比例"};
                XSSFRow sheet1Row12 = sheet1.createRow(provinceList.size() + 5 + customerStatList.size() + 4 + customerTypeList.size() + 4 +
                        otherCustomerTypeCount + maintainBigProblemList.size() + 4 + otherMainList + stationTypeList.size() + 4 + brandArrayList.size() +
                        3 + otherBrandArrayList.size() + 4 + stationAppealList.size() + 4 + paintBrandList.size() + 4);
                //创建标题
                for (int i = 0; i < sheet1OtherPaintBrandList.length; i++) {
                    sheet1Cell21 = sheet1Row12.createCell(i);
                    sheet1Cell21.setCellValue(sheet1OtherPaintBrandList[i]);
                    sheet1Cell21.setCellStyle(titleStyle13);
                }
                countValue = serviceResponseVo.getServiceReport().getCountPerson();
//            for (int i = 0; i < provinceList.size(); i++) {
//                countValue += Integer.valueOf(provinceList.get(i)[1]);
//            }
                for (int i = 0; i < otherPaintBrandList.size(); i++) {
                    String doubleValue = doubleGeTowDecimal(Double.valueOf(otherPaintBrandList.get(i)[1]) / countValue);
                    otherPaintBrandList.get(i)[2] = doubleValue;
                }
                strings = new String[3];
                strings[0] = "有效报告数";
                strings[1] = String.valueOf(countValue);
                strings[2] = "1";
                otherPaintBrandList.add(strings);

                XSSFCell sheet1Cell22 = null;
                for (int i = 0; i < otherPaintBrandList.size(); i++) {
                    sheet1Row10 = sheet1.createRow(i + provinceList.size() + 5 + customerStatList.size() + 4 + customerTypeList.size() + 4 +
                            otherCustomerTypeCount + maintainBigProblemList.size() + 4 + otherMainList + stationTypeList.size() + 4 + brandArrayList.size() + 4 +
                            otherBrandArrayList.size() + 4 + stationAppealList.size() + 4 + paintBrandList.size() + 4);
                    for (int j = 0; j < otherPaintBrandList.get(i).length; j++) {
                        sheet1Cell22 = sheet1Row10.createCell(j);
                        //将内容按顺序赋给对应的列对象
                        Double doubleValue = 0D;
                        if (j == otherPaintBrandList.get(i).length - 1) {
                            if (i % 2 == 0) {
                                if (j == 2) {
                                    doubleValue = Double.valueOf(otherPaintBrandList.get(i)[j]);
                                    titleStyle22222222.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                                sheet1Cell22.setCellStyle(titleStyle22222222);
                            } else {
                                if (j == 2) {
                                    if (StringUtils.isNotEmpty(otherPaintBrandList.get(i)[j])) {
                                        doubleValue = Double.valueOf(otherPaintBrandList.get(i)[j]);
                                        titleStyle55555555.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                    }
                                }
                                sheet1Cell22.setCellStyle(titleStyle55555555);
                            }
                            try {
                                if (j == 2) {
                                    sheet1Cell22.setCellValue(doubleValue);
                                } else {
                                    sheet1Cell22.setCellValue(Long.valueOf(otherPaintBrandList.get(i)[j]));
                                }
                            } catch (Exception e) {
                                sheet1Cell22.setCellValue(otherPaintBrandList.get(i)[j]);
                            }

                        } else {
                            if (i % 2 == 0) {
                                if (j == 2) {
                                    doubleValue = Double.valueOf(otherPaintBrandList.get(i)[j]);
                                    titleStyle33333333.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                                sheet1Cell22.setCellStyle(titleStyle33333333);
                            } else {
                                if (j == 2) {
                                    if (StringUtils.isNotEmpty(otherPaintBrandList.get(i)[j])) {
                                        doubleValue = Double.valueOf(otherPaintBrandList.get(i)[j]);
                                        titleStyle44444444.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                    }
                                }
                                sheet1Cell22.setCellStyle(titleStyle44444444);
                            }
                            try {
                                if (j == 2) {
                                    sheet1Cell22.setCellValue(doubleValue);
                                } else {
                                    sheet1Cell22.setCellValue(Long.valueOf(otherPaintBrandList.get(i)[j]));
                                }
                            } catch (Exception e) {
                                sheet1Cell22.setCellValue(otherPaintBrandList.get(i)[j]);
                            }
                        }
                    }
                }
            }

            //品牌定位
            XSSFColor myColor24 = new XSSFColor(new java.awt.Color(155, 187, 89));
            XSSFCellStyle titleStyle14 = workbook.createCellStyle();
            titleStyle14.setFillForegroundColor(myColor24);
            titleStyle14.setAlignment(HorizontalAlignment.CENTER);
            titleStyle14.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle14.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle14.setFont(font);
            titleStyle14.setBorderLeft(BorderStyle.THIN);
            titleStyle14.setLeftBorderColor(myColor24);
            titleStyle14.setBorderRight(BorderStyle.THIN);
            titleStyle14.setRightBorderColor(myColor24);
            titleStyle14.setBorderTop(BorderStyle.THIN);
            titleStyle14.setTopBorderColor(myColor24);

            XSSFColor myColor25 = new XSSFColor(new java.awt.Color(216, 228, 188));
            XSSFCellStyle titleStyle222222222 = workbook.createCellStyle();
            titleStyle222222222.setAlignment(HorizontalAlignment.CENTER);
            titleStyle222222222.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle222222222.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle222222222.setFillForegroundColor(myColor25);
            titleStyle222222222.setBorderTop(BorderStyle.THIN);
            titleStyle222222222.setBorderBottom(BorderStyle.THIN);
            titleStyle222222222.setBorderRight(BorderStyle.THIN);
            titleStyle222222222.setTopBorderColor(myColor25);
            titleStyle222222222.setFont(font2);

            XSSFCellStyle titleStyle333333333 = workbook.createCellStyle();
            titleStyle333333333.setAlignment(HorizontalAlignment.CENTER);
            titleStyle333333333.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle333333333.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle333333333.setFillForegroundColor(myColor25);
            titleStyle333333333.setBorderTop(BorderStyle.THIN);
            titleStyle333333333.setBorderBottom(BorderStyle.THIN);
            titleStyle333333333.setBorderRight(BorderStyle.NONE);
            titleStyle333333333.setTopBorderColor(myColor25);
            titleStyle333333333.setFont(font2);

            XSSFColor myColor26 = new XSSFColor(new java.awt.Color(235, 241, 222));
            XSSFCellStyle titleStyle444444444 = workbook.createCellStyle();
            titleStyle444444444.setAlignment(HorizontalAlignment.CENTER);
            titleStyle444444444.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle444444444.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle444444444.setFillForegroundColor(myColor26);
            titleStyle444444444.setBorderTop(BorderStyle.THIN);
            titleStyle444444444.setBorderBottom(BorderStyle.THIN);
            titleStyle444444444.setBorderRight(BorderStyle.NONE);
            titleStyle444444444.setTopBorderColor(myColor26);
            titleStyle444444444.setFont(font2);

            XSSFCellStyle titleStyle555555555 = workbook.createCellStyle();
            titleStyle555555555.setAlignment(HorizontalAlignment.CENTER);
            titleStyle555555555.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle555555555.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle555555555.setFillForegroundColor(myColor26);
            titleStyle555555555.setBorderTop(BorderStyle.THIN);
            titleStyle555555555.setBorderBottom(BorderStyle.THIN);
            titleStyle555555555.setBorderRight(BorderStyle.THIN);
            titleStyle555555555.setTopBorderColor(myColor26);
            titleStyle555555555.setFont(font2);

            List<String[]> productPositionList = serviceResponseVo.getServiceReport().getProductPositionList();
            XSSFCell sheet1Cell23 = null;
            String[] sheet1ProductPositionTitle = {"品牌定位", "站点数量", "比例"};
            XSSFRow sheet1Row13 = sheet1.createRow(provinceList.size() + 5 + customerStatList.size() + 4 + customerTypeList.size() + 4 +
                    otherCustomerTypeCount + maintainBigProblemList.size() + 4 + otherMainList + stationTypeList.size() + 4 + brandArrayList.size() +
                    3 + otherBrandArrayList.size() + 4 + stationAppealList.size() + 4 + paintBrandList.size() + 4 + otherPaintBrandCount);
            //创建标题
            for (int i = 0; i < sheet1ProductPositionTitle.length; i++) {
                sheet1Cell23 = sheet1Row13.createCell(i);
                sheet1Cell23.setCellValue(sheet1ProductPositionTitle[i]);
                sheet1Cell23.setCellStyle(titleStyle14);
            }
            countValue = serviceResponseVo.getServiceReport().getCountPerson();
//            for (int i = 0; i < provinceList.size(); i++) {
//                countValue += Integer.valueOf(provinceList.get(i)[1]);
//            }
            for (int i = 0; i < productPositionList.size(); i++) {
                productPositionList.get(i)[2] = doubleGeTowDecimal(Double.valueOf(productPositionList.get(i)[1]) / countValue);
            }
            strings = new String[3];
            strings[0] = "有效报告数";
            strings[1] = String.valueOf(countValue);
            strings[2] = "1";
            productPositionList.add(strings);

            XSSFCell sheet1Cell24 = null;
            for (int i = 0; i < productPositionList.size(); i++) {
                sheet1Row13 = sheet1.createRow(i + provinceList.size() + 5 + customerStatList.size() + 4 + customerTypeList.size() + 4 +
                        otherCustomerTypeCount + maintainBigProblemList.size() + 4 + otherMainList + stationTypeList.size() + 4 + brandArrayList.size() + 4 +
                        otherBrandArrayList.size() + 4 + stationAppealList.size() + 4 + paintBrandList.size() + 4 + otherPaintBrandCount);
                for (int j = 0; j < productPositionList.get(i).length; j++) {
                    sheet1Cell24 = sheet1Row13.createCell(j);
                    //将内容按顺序赋给对应的列对象
                    Double doubleValue = 0D;
                    if (j == productPositionList.get(i).length - 1) {
                        if (i % 2 == 0) {
                            if (j == 2) {
                                doubleValue = Double.valueOf(productPositionList.get(i)[j]);
                                titleStyle222222222.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                            }
                            sheet1Cell24.setCellStyle(titleStyle222222222);
                        } else {
                            if (j == 2) {
                                if (StringUtils.isNotEmpty(productPositionList.get(i)[j])) {
                                    doubleValue = Double.valueOf(productPositionList.get(i)[j]);
                                    titleStyle555555555.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                            }
                            sheet1Cell24.setCellStyle(titleStyle555555555);
                        }
                        try {
                            if (j == 2) {
                                sheet1Cell24.setCellValue(doubleValue);
                            } else {
                                sheet1Cell24.setCellValue(Long.valueOf(productPositionList.get(i)[j]));
                            }
                        } catch (Exception e) {
                            sheet1Cell24.setCellValue(productPositionList.get(i)[j]);
                        }

                    } else {
                        if (i % 2 == 0) {
                            if (j == 2) {
                                doubleValue = Double.valueOf(productPositionList.get(i)[j]);
                                titleStyle333333333.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                            }
                            sheet1Cell24.setCellStyle(titleStyle333333333);
                        } else {
                            if (j == 2) {
                                if (StringUtils.isNotEmpty(productPositionList.get(i)[j])) {
                                    doubleValue = Double.valueOf(productPositionList.get(i)[j]);
                                    titleStyle444444444.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                            }
                            sheet1Cell24.setCellStyle(titleStyle444444444);
                        }
                        try {
                            if (j == 2) {
                                sheet1Cell24.setCellValue(doubleValue);
                            } else {
                                sheet1Cell24.setCellValue(Long.valueOf(productPositionList.get(i)[j]));
                            }
                        } catch (Exception e) {
                            sheet1Cell24.setCellValue(productPositionList.get(i)[j]);
                        }
                    }
                }
            }

            //服务类型
            XSSFColor myColor27 = new XSSFColor(new java.awt.Color(192, 80, 77));
            XSSFCellStyle titleStyle15 = workbook.createCellStyle();
            titleStyle15.setFillForegroundColor(myColor27);
            titleStyle15.setAlignment(HorizontalAlignment.CENTER);
            titleStyle15.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle15.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle15.setFont(font);
            titleStyle15.setBorderLeft(BorderStyle.THIN);
            titleStyle15.setLeftBorderColor(myColor27);
            titleStyle15.setBorderRight(BorderStyle.THIN);
            titleStyle15.setRightBorderColor(myColor27);
            titleStyle15.setBorderTop(BorderStyle.THIN);
            titleStyle15.setTopBorderColor(myColor27);

            XSSFColor myColor28 = new XSSFColor(new java.awt.Color(230, 184, 183));
            XSSFCellStyle titleStyle2222222222 = workbook.createCellStyle();
            titleStyle2222222222.setAlignment(HorizontalAlignment.CENTER);
            titleStyle2222222222.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle2222222222.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle2222222222.setFillForegroundColor(myColor28);
            titleStyle2222222222.setBorderTop(BorderStyle.THIN);
            titleStyle2222222222.setBorderBottom(BorderStyle.THIN);
            titleStyle2222222222.setBorderRight(BorderStyle.THIN);
            titleStyle2222222222.setTopBorderColor(myColor28);
            titleStyle2222222222.setFont(font2);

            XSSFCellStyle titleStyle3333333333 = workbook.createCellStyle();
            titleStyle3333333333.setAlignment(HorizontalAlignment.CENTER);
            titleStyle3333333333.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle3333333333.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle3333333333.setFillForegroundColor(myColor28);
            titleStyle3333333333.setBorderTop(BorderStyle.THIN);
            titleStyle3333333333.setBorderBottom(BorderStyle.THIN);
            titleStyle3333333333.setBorderRight(BorderStyle.NONE);
            titleStyle3333333333.setTopBorderColor(myColor28);
            titleStyle3333333333.setFont(font2);

            XSSFColor myColor29 = new XSSFColor(new java.awt.Color(242, 220, 219));
            XSSFCellStyle titleStyle4444444444 = workbook.createCellStyle();
            titleStyle4444444444.setAlignment(HorizontalAlignment.CENTER);
            titleStyle4444444444.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle4444444444.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle4444444444.setFillForegroundColor(myColor29);
            titleStyle4444444444.setBorderTop(BorderStyle.THIN);
            titleStyle4444444444.setBorderBottom(BorderStyle.THIN);
            titleStyle4444444444.setBorderRight(BorderStyle.NONE);
            titleStyle4444444444.setTopBorderColor(myColor29);
            titleStyle4444444444.setFont(font2);

            XSSFCellStyle titleStyle5555555555 = workbook.createCellStyle();
            titleStyle5555555555.setAlignment(HorizontalAlignment.CENTER);
            titleStyle5555555555.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle5555555555.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle5555555555.setFillForegroundColor(myColor29);
            titleStyle5555555555.setBorderTop(BorderStyle.THIN);
            titleStyle5555555555.setBorderBottom(BorderStyle.THIN);
            titleStyle5555555555.setBorderRight(BorderStyle.THIN);
            titleStyle5555555555.setTopBorderColor(myColor29);
            titleStyle5555555555.setFont(font2);

            List<String[]> targetList = serviceResponseVo.getServiceReport().getVstarServiceReport_target().getTarget();
            XSSFCell sheet1Cell25 = null;
            String[] sheet1Target = {"服务类型", "站点数量", "比例"};
            XSSFRow sheet1Row14 = sheet1.createRow(provinceList.size() + 5 + customerStatList.size() + 4 + customerTypeList.size() + 4 +
                    otherCustomerTypeCount + maintainBigProblemList.size() + 4 + otherMainList + stationTypeList.size() + 4 + brandArrayList.size() +
                    3 + otherBrandArrayList.size() + 4 + stationAppealList.size() + 4 + paintBrandList.size() + 4 + otherPaintBrandCount + productPositionList.size() + 4);
            //创建标题
            for (int i = 0; i < sheet1Target.length; i++) {
                sheet1Cell25 = sheet1Row14.createCell(i);
                sheet1Cell25.setCellValue(sheet1Target[i]);
                sheet1Cell25.setCellStyle(titleStyle15);
            }
            countValue = serviceResponseVo.getServiceReport().getCountPerson();
//            for (int i = 0; i < provinceList.size(); i++) {
//                countValue += Integer.valueOf(provinceList.get(i)[1]);
//            }
            for (int i = 0; i < targetList.size(); i++) {
                targetList.get(i)[2] = doubleGeTowDecimal(Double.valueOf(targetList.get(i)[1]) / countValue);
            }
            strings = new String[3];
            strings[0] = "有效报告数";
            strings[1] = String.valueOf(countValue);
            strings[2] = "1";
            targetList.add(strings);

            XSSFCell sheet1Cell26 = null;
            for (int i = 0; i < targetList.size(); i++) {
                sheet1Row14 = sheet1.createRow(i + provinceList.size() + 5 + customerStatList.size() + 4 + customerTypeList.size() + 4 +
                        otherCustomerTypeCount + maintainBigProblemList.size() + 4 + otherMainList + stationTypeList.size() + 4 + brandArrayList.size() + 4 +
                        otherBrandArrayList.size() + 4 + stationAppealList.size() + 4 + paintBrandList.size() + 4 + otherPaintBrandCount + productPositionList.size() + 4);
                for (int j = 0; j < targetList.get(i).length; j++) {
                    sheet1Cell26 = sheet1Row14.createCell(j);
                    //将内容按顺序赋给对应的列对象
                    Double doubleValue = 0D;
                    if (j == targetList.get(i).length - 1) {
                        if (i % 2 == 0) {
                            if (j == 2) {
                                doubleValue = Double.valueOf(targetList.get(i)[j]);
                                titleStyle2222222222.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                            }
                            sheet1Cell26.setCellStyle(titleStyle2222222222);
                        } else {
                            if (j == 2) {
                                if (StringUtils.isNotEmpty(targetList.get(i)[j])) {
                                    doubleValue = Double.valueOf(targetList.get(i)[j]);
                                    titleStyle5555555555.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                            }
                            sheet1Cell26.setCellStyle(titleStyle5555555555);
                        }
                        try {
                            if (j == 2) {
                                sheet1Cell26.setCellValue(doubleValue);
                            } else {
                                sheet1Cell26.setCellValue(Long.valueOf(targetList.get(i)[j]));
                            }
                        } catch (Exception e) {
                            sheet1Cell26.setCellValue(targetList.get(i)[j]);
                        }

                    } else {
                        if (i % 2 == 0) {
                            if (j == 2) {
                                doubleValue = Double.valueOf(targetList.get(i)[j]);
                                titleStyle3333333333.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                            }
                            sheet1Cell26.setCellStyle(titleStyle3333333333);
                        } else {
                            if (j == 2) {
                                if (StringUtils.isNotEmpty(targetList.get(i)[j])) {
                                    doubleValue = Double.valueOf(targetList.get(i)[j]);
                                    titleStyle4444444444.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                            }
                            sheet1Cell26.setCellStyle(titleStyle4444444444);
                        }
                        try {
                            if (j == 2) {
                                sheet1Cell26.setCellValue(doubleValue);
                            } else {
                                sheet1Cell26.setCellValue(Long.valueOf(targetList.get(i)[j]));
                            }
                        } catch (Exception e) {
                            sheet1Cell26.setCellValue(targetList.get(i)[j]);
                        }
                    }
                }
            }

            // 其他服务类型
            List<String[]> otherTarget = serviceResponseVo.getServiceReport().getVstarServiceReport_target().getOtherTarget();
            XSSFCell sheet1Cell27 = null;
            String[] sheet1OtherTarget = {"客户名称", "客户编号", "日期", "服务项目", "外勤人员", "比例"};
            XSSFRow sheet1Row15 = sheet1.createRow(provinceList.size() + 5 + customerStatList.size() + 4 + customerTypeList.size() + 4 +
                    otherCustomerTypeCount + maintainBigProblemList.size() + 4 + otherMainList + stationTypeList.size() + 4 + brandArrayList.size() +
                    3 + otherBrandArrayList.size() + 4 + stationAppealList.size() + 4 + paintBrandList.size() + 4 + otherPaintBrandCount + productPositionList.size() + 4 + targetList.size() + 3);
            //创建标题
            for (int i = 0; i < sheet1OtherTarget.length; i++) {
                sheet1Cell27 = sheet1Row15.createCell(i);
                sheet1Cell27.setCellValue(sheet1OtherTarget[i]);
                sheet1Cell27.setCellStyle(titleStyle15);
            }
            int otherServiceTypeCount = serviceResponseVo.getServiceReport().getCountPerson();
            for (int i = 0; i < otherTarget.size(); i++) {
                otherTarget.get(i)[5] = doubleGeTowDecimal(Double.valueOf(1) / otherServiceTypeCount);
            }
            strings = new String[6];
            strings[0] = "有效报告数";
            strings[1] = String.valueOf(otherServiceTypeCount);
            strings[2] = "";
            strings[3] = "";
            strings[4] = "";
            strings[5] = "1";
            otherTarget.add(strings);

            XSSFCell sheet1Cell28 = null;
            for (int i = 0; i < otherTarget.size(); i++) {
                sheet1Row15 = sheet1.createRow(i + provinceList.size() + 5 + customerStatList.size() + 4 + customerTypeList.size() + 4 +
                        otherCustomerTypeCount + maintainBigProblemList.size() + 4 + otherMainList + stationTypeList.size() + 4 + brandArrayList.size() +
                        3 + otherBrandArrayList.size() + 4 + stationAppealList.size() + 4 + paintBrandList.size() + 4 + otherPaintBrandCount + productPositionList.size() + 4 + targetList.size() + 4);
                for (int j = 0; j < otherTarget.get(i).length; j++) {
                    sheet1Cell28 = sheet1Row15.createCell(j);
                    //将内容按顺序赋给对应的列对象
                    Double doubleValue = 0D;
                    if (j == otherTarget.get(i).length - 1) {
                        if (i % 2 == 0) {
                            if (j == 5) {
                                doubleValue = Double.valueOf(otherTarget.get(i)[j]);
                                titleStyle2222222222.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                            }
                            sheet1Cell28.setCellStyle(titleStyle2222222222);
                        } else {
                            if (j == 5) {
                                if (StringUtils.isNotEmpty(otherTarget.get(i)[j])) {
                                    doubleValue = Double.valueOf(otherTarget.get(i)[j]);
                                    titleStyle5555555555.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                            }
                            sheet1Cell28.setCellStyle(titleStyle5555555555);
                        }
                        try {
                            if (j == 5) {
                                sheet1Cell28.setCellValue(doubleValue);
                            } else {
                                sheet1Cell28.setCellValue(Long.valueOf(otherTarget.get(i)[j]));
                            }
                        } catch (Exception e) {
                            sheet1Cell28.setCellValue(otherTarget.get(i)[j]);
                        }
                    } else {
                        if (i % 2 == 0) {
                            if (j == 5) {
                                doubleValue = Double.valueOf(otherTarget.get(i)[j]);
                                titleStyle3333333333.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                            }
                            sheet1Cell28.setCellStyle(titleStyle3333333333);
                        } else {
                            if (j == 5) {
                                if (StringUtils.isNotEmpty(otherTarget.get(i)[j])) {
                                    doubleValue = Double.valueOf(otherTarget.get(i)[j]);
                                    titleStyle4444444444.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                            }
                            sheet1Cell28.setCellStyle(titleStyle4444444444);
                        }
                        try {
                            if (j == 5) {
                                sheet1Cell28.setCellValue(doubleValue);
                            } else {
                                sheet1Cell28.setCellValue(Long.valueOf(otherTarget.get(i)[j]));
                            }
                        } catch (Exception e) {
                            sheet1Cell28.setCellValue(otherTarget.get(i)[j]);
                        }
                    }
                }
            }

            //服务结果
            List<String[]> resultList = new ArrayList<>();
            Integer complete = serviceResponseVo.getServiceReport().getVstarServiceReport_results().getCompleteReport().size();
            String[] str = new String[3];
            str[0] = "已完成";
            str[1] = complete.toString();
            str[2] = "";
            resultList.add(str);
            Integer complete_NeedOthersToFollowUp = serviceResponseVo.getServiceReport().getVstarServiceReport_results().getComplete_needFollowUp().size();
            str = new String[3];
            str[0] = "已完成，但需其他部门跟进";
            str[1] = complete_NeedOthersToFollowUp.toString();
            str[2] = "";
            resultList.add(str);
            Integer noComplete_NeedToFollowUp = serviceResponseVo.getServiceReport().getVstarServiceReport_results().getNoComplete_needFollowUp().size();
            str = new String[3];
            str[0] = "没有完成，需后续跟进";
            str[1] = noComplete_NeedToFollowUp.toString();
            str[2] = "";
            resultList.add(str);
            Integer noComplete_UnableToFollowUp = serviceResponseVo.getServiceReport().getVstarServiceReport_results().getNoComplete_UnFollowUp().size();
            str = new String[3];
            str[0] = "没有完成，无法后续跟进";
            str[1] = noComplete_UnableToFollowUp.toString();
            str[2] = "";
            resultList.add(str);
            Integer manageProductCount = serviceResponseVo.getServiceReport().getVstarServiceReport_results().getManageProduct().size();
            str = new String[3];
            str[0] = "处理产品问题（投诉）";
            str[1] = manageProductCount.toString();
            str[2] = "";
            resultList.add(str);
            Integer planReport = serviceResponseVo.getServiceReport().getVstarServiceReport_results().getPlanReport().size();
            str = new String[3];
            str[0] = "计划进行该项工作，尚未开展";
            str[1] = planReport.toString();
            str[2] = "";
            resultList.add(str);


            XSSFCell sheet1Cell29 = null;
            String[] sheet1Result = {"服务结果", "站点数量", "比例"};
            XSSFRow sheet1Row16 = sheet1.createRow(provinceList.size() + 5 + customerStatList.size() + 4 + customerTypeList.size() + 4 +
                    otherCustomerTypeCount + maintainBigProblemList.size() + 4 + otherMainList + stationTypeList.size() + 4 + brandArrayList.size() +
                    3 + otherBrandArrayList.size() + 4 + stationAppealList.size() + 4 + paintBrandList.size() + 4 + otherPaintBrandCount +
                    productPositionList.size() + 4 + targetList.size() + 4 + otherTarget.size() + 3);
            //创建标题
            for (int i = 0; i < sheet1Result.length; i++) {
                sheet1Cell29 = sheet1Row16.createCell(i);
                sheet1Cell29.setCellValue(sheet1Result[i]);
                sheet1Cell29.setCellStyle(titleStyle10);
            }
            countValue = serviceResponseVo.getServiceReport().getCountPerson();
//            for (int i = 0; i < provinceList.size(); i++) {
//                countValue += Integer.valueOf(provinceList.get(i)[1]);
//            }
            for (int i = 0; i < resultList.size(); i++) {
                resultList.get(i)[2] = doubleGeTowDecimal(Double.valueOf(resultList.get(i)[1]) / countValue);
            }
            strings = new String[3];
            strings[0] = "有效报告数";
            strings[1] = String.valueOf(countValue);
            strings[2] = "1";
            resultList.add(strings);

            XSSFCell sheet1Cell30 = null;
            for (int i = 0; i < resultList.size(); i++) {
                sheet1Row16 = sheet1.createRow(i + provinceList.size() + 5 + customerStatList.size() + 4 + customerTypeList.size() + 4 +
                        otherCustomerTypeCount + maintainBigProblemList.size() + 4 + otherMainList + stationTypeList.size() + 4 + brandArrayList.size() +
                        3 + otherBrandArrayList.size() + 4 + stationAppealList.size() + 4 + paintBrandList.size() + 4 + otherPaintBrandCount +
                        productPositionList.size() + 4 + targetList.size() + 4 + otherTarget.size() + 4);
                for (int j = 0; j < resultList.get(i).length; j++) {
                    sheet1Cell30 = sheet1Row16.createCell(j);
                    //将内容按顺序赋给对应的列对象
                    Double doubleValue = 0D;
                    if (j == resultList.get(i).length - 1) {
                        if (i % 2 == 0) {
                            if (j == 2) {
                                doubleValue = Double.valueOf(resultList.get(i)[j]);
                                titleStyle22222.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                            }
                            sheet1Cell30.setCellStyle(titleStyle22222);
                        } else {
                            if (j == 2) {
                                if (StringUtils.isNotEmpty(resultList.get(i)[j])) {
                                    doubleValue = Double.valueOf(resultList.get(i)[j]);
                                    titleStyle55555.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                            }
                            sheet1Cell30.setCellStyle(titleStyle55555);
                        }
                        try {
                            if (j == 2) {
                                sheet1Cell30.setCellValue(doubleValue);
                            } else {
                                sheet1Cell30.setCellValue(Long.valueOf(resultList.get(i)[j]));
                            }
                        } catch (Exception e) {
                            sheet1Cell30.setCellValue(resultList.get(i)[j]);
                        }
                    } else {
                        if (i % 2 == 0) {
                            if (j == 2) {
                                doubleValue = Double.valueOf(resultList.get(i)[j]);
                                titleStyle33333.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                            }
                            sheet1Cell30.setCellStyle(titleStyle33333);
                        } else {
                            if (j == 2) {
                                if (StringUtils.isNotEmpty(resultList.get(i)[j])) {
                                    doubleValue = Double.valueOf(resultList.get(i)[j]);
                                    titleStyle44444.setDataFormat(xssfDataFormat.getFormat("0.00%"));
                                }
                            }
                            sheet1Cell30.setCellStyle(titleStyle44444);
                        }
                        try {
                            if (j == 2) {
                                sheet1Cell30.setCellValue(doubleValue);
                            } else {
                                sheet1Cell30.setCellValue(Long.valueOf(resultList.get(i)[j]));
                            }
                        } catch (Exception e) {
                            sheet1Cell30.setCellValue(resultList.get(i)[j]);
                        }
                    }
                }
            }

            /**
             * 创建第三个个sheet
             */
            XSSFColor sheet2MyColor = new XSSFColor(new java.awt.Color(79, 129, 189));
            XSSFCellStyle sheet2TitleStyle1 = workbook.createCellStyle();
            sheet2TitleStyle1.setFillForegroundColor(sheet2MyColor);
            sheet2TitleStyle1.setAlignment(HorizontalAlignment.CENTER);
            sheet2TitleStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
            sheet2TitleStyle1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            sheet2TitleStyle1.setFont(font);
            sheet2TitleStyle1.setBorderLeft(BorderStyle.THIN);
            sheet2TitleStyle1.setLeftBorderColor(sheet2MyColor);
            sheet2TitleStyle1.setBorderRight(BorderStyle.THIN);
            sheet2TitleStyle1.setRightBorderColor(sheet2MyColor);
            sheet2TitleStyle1.setBorderTop(BorderStyle.THIN);
            sheet2TitleStyle1.setTopBorderColor(sheet2MyColor);

            XSSFColor sheet2MyColor1 = new XSSFColor(new java.awt.Color(220, 230, 241));
            XSSFCellStyle sheet2TitleStyle2 = workbook.createCellStyle();
            sheet2TitleStyle2.setAlignment(HorizontalAlignment.CENTER);
            sheet2TitleStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
            sheet2TitleStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            sheet2TitleStyle2.setFillForegroundColor(sheet2MyColor1);
            sheet2TitleStyle2.setBorderTop(BorderStyle.THIN);
            sheet2TitleStyle2.setBorderBottom(BorderStyle.THIN);
            sheet2TitleStyle2.setBorderRight(BorderStyle.THIN);
            sheet2TitleStyle2.setTopBorderColor(sheet2MyColor1);
            sheet2TitleStyle2.setFont(font2);

            XSSFCellStyle sheet2TitleStyle3 = workbook.createCellStyle();
            sheet2TitleStyle3.setAlignment(HorizontalAlignment.CENTER);
            sheet2TitleStyle3.setVerticalAlignment(VerticalAlignment.CENTER);
            sheet2TitleStyle3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            sheet2TitleStyle3.setFillForegroundColor(sheet2MyColor1);
            sheet2TitleStyle3.setBorderTop(BorderStyle.THIN);
            sheet2TitleStyle3.setBorderBottom(BorderStyle.THIN);
            sheet2TitleStyle3.setBorderRight(BorderStyle.NONE);
            sheet2TitleStyle3.setTopBorderColor(sheet2MyColor1);
            sheet2TitleStyle3.setFont(font2);

            XSSFColor sheet2MyColor2 = new XSSFColor(new java.awt.Color(255, 255, 255));
            XSSFCellStyle sheet2TitleStyle4 = workbook.createCellStyle();
            sheet2TitleStyle4.setAlignment(HorizontalAlignment.CENTER);
            sheet2TitleStyle4.setVerticalAlignment(VerticalAlignment.CENTER);
            sheet2TitleStyle4.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            sheet2TitleStyle4.setFillForegroundColor(sheet2MyColor2);
            sheet2TitleStyle4.setBorderTop(BorderStyle.THIN);
            sheet2TitleStyle4.setBorderBottom(BorderStyle.THIN);
            sheet2TitleStyle4.setBorderRight(BorderStyle.NONE);
            sheet2TitleStyle4.setTopBorderColor(sheet2MyColor2);
            sheet2TitleStyle4.setFont(font2);

            XSSFCellStyle sheet2TitleStyle5 = workbook.createCellStyle();
            sheet2TitleStyle5.setAlignment(HorizontalAlignment.CENTER);
            sheet2TitleStyle5.setVerticalAlignment(VerticalAlignment.CENTER);
            sheet2TitleStyle5.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            sheet2TitleStyle5.setFillForegroundColor(sheet2MyColor2);
            sheet2TitleStyle5.setBorderTop(BorderStyle.THIN);
            sheet2TitleStyle5.setBorderBottom(BorderStyle.THIN);
            sheet2TitleStyle5.setBorderRight(BorderStyle.THIN);
            sheet2TitleStyle5.setTopBorderColor(sheet2MyColor2);
            sheet2TitleStyle5.setFont(font2);

            XSSFCellStyle sheet2TitleStyle6 = workbook.createCellStyle();
            sheet2TitleStyle6.setFont(font2);
            sheet2TitleStyle6.setAlignment(HorizontalAlignment.CENTER);
            sheet2TitleStyle6.setVerticalAlignment(VerticalAlignment.CENTER);

            XSSFColor sheet2MyColor4 = new XSSFColor(new java.awt.Color(146, 208, 80));
            XSSFCellStyle sheet2TitleStyle7 = workbook.createCellStyle();
            sheet2TitleStyle7.setFillForegroundColor(sheet2MyColor4);
            sheet2TitleStyle7.setAlignment(HorizontalAlignment.CENTER);
            sheet2TitleStyle7.setVerticalAlignment(VerticalAlignment.CENTER);
            sheet2TitleStyle7.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            sheet2TitleStyle7.setFont(font2);
            sheet2TitleStyle7.setBorderLeft(BorderStyle.THIN);
            sheet2TitleStyle7.setLeftBorderColor(sheet2MyColor4);
            sheet2TitleStyle7.setBorderRight(BorderStyle.THIN);
            sheet2TitleStyle7.setRightBorderColor(sheet2MyColor4);
            sheet2TitleStyle7.setBorderTop(BorderStyle.THIN);
            sheet2TitleStyle7.setTopBorderColor(sheet2MyColor4);

            XSSFSheet sheet2 = workbook.createSheet("详情");
            for (int i = 0; i < 19; i++) {
                if (i == 6) {
                    continue;
                }
                sheet2.setColumnWidth(i, 15 * 2 * 256);
            }
            sheet2.setColumnWidth(13, 100 * 2 * 256);
            // 其他服务类型
            XSSFRow sheet2RowIndex0 = sheet2.createRow(0);
            XSSFCell manageProduct = sheet2RowIndex0.createCell(0);
            manageProduct.setCellStyle(sheet2TitleStyle7);
            manageProduct.setCellValue("处理产品问题（投诉）");
            for (int i = 1; i < 14; i++) {
                manageProduct = sheet2RowIndex0.createCell(i);
                manageProduct.setCellStyle(sheet2TitleStyle7);
            }
            XSSFRow sheet2Row1 = sheet2.createRow(1);
            XSSFCell sheet2Cell1 = null;
            String[] sheet2Title = {"日期", "工作人员", "所属省份", "客户名称", "维修站类型", "具体品牌", "客户代码", "客户状态", "客户类型", "油漆品牌", "品牌定位", "服务商", "重点工作分类", "服务自我评估"};
            List<String[]> manageProductList = serviceResponseVo.getServiceReport().getVstarServiceReport_results().getManageProduct();
            //创建标题
            for (int i = 0; i < sheet2Title.length; i++) {
                sheet2Cell1 = sheet2Row1.createCell(i);
                sheet2Cell1.setCellValue(sheet2Title[i]);
                sheet2Cell1.setCellStyle(sheet2TitleStyle1);
            }

            XSSFCell sheet2Cell2 = null;
            for (int i = 0; i < manageProductList.size(); i++) {
                sheet2Row1 = sheet2.createRow(i + 2);
                for (int j = 0; j < manageProductList.get(i).length; j++) {
                    sheet2Cell2 = sheet2Row1.createCell(j);
                    //将内容按顺序赋给对应的列对象
                    if (j == manageProductList.get(i).length - 1) {
                        if (i % 2 == 0) {
                            sheet2Cell2.setCellStyle(sheet2TitleStyle2);
                        } else {
                            sheet2Cell2.setCellStyle(sheet2TitleStyle5);
                        }
                        sheet2Cell2.setCellValue(manageProductList.get(i)[j]);
                    } else {
                        if (i % 2 == 0) {
                            sheet2Cell2.setCellStyle(sheet2TitleStyle3);
                        } else {
                            sheet2Cell2.setCellStyle(sheet2TitleStyle4);
                        }
                        sheet2Cell2.setCellValue(manageProductList.get(i)[j]);
                    }
                }
            }

            //没有完成，无法后续跟进
            List<String[]> noComplete_UnFollowUpList = serviceResponseVo.getServiceReport().getVstarServiceReport_results().getNoComplete_UnFollowUp();
            XSSFRow sheet2RowIndex1 = sheet2.createRow(manageProductList.size() + 2 + 3);
            XSSFCell noComplete_UnFollowUp = sheet2RowIndex1.createCell(0);
            noComplete_UnFollowUp.setCellStyle(sheet2TitleStyle7);
            noComplete_UnFollowUp.setCellValue("没有完成，无法后续跟进");
            for (int i = 1; i < 14; i++) {
                noComplete_UnFollowUp = sheet2RowIndex1.createCell(i);
                noComplete_UnFollowUp.setCellStyle(sheet2TitleStyle7);
            }
            //创建标题
            XSSFCell sheet2Cell3 = null;
            XSSFRow sheet2Row3 = sheet2.createRow(manageProductList.size() + 2 + 4);
            for (int i = 0; i < sheet2Title.length; i++) {
                sheet2Cell3 = sheet2Row3.createCell(i);
                sheet2Cell3.setCellValue(sheet2Title[i]);
                sheet2Cell3.setCellStyle(sheet2TitleStyle1);
            }
            XSSFCell sheet2Cell4 = null;
            XSSFRow sheet2Row2 = null;
            for (int i = 0; i < noComplete_UnFollowUpList.size(); i++) {
                sheet2Row2 = sheet2.createRow(i + manageProductList.size() + 7);
                for (int j = 0; j < noComplete_UnFollowUpList.get(i).length; j++) {
                    sheet2Cell4 = sheet2Row2.createCell(j);
                    //将内容按顺序赋给对应的列对象
                    if (j == noComplete_UnFollowUpList.get(i).length - 1) {
                        if (i % 2 == 0) {
                            sheet2Cell4.setCellStyle(sheet2TitleStyle2);
                        } else {
                            sheet2Cell4.setCellStyle(sheet2TitleStyle5);
                        }
                        sheet2Cell4.setCellValue(noComplete_UnFollowUpList.get(i)[j]);
                    } else {
                        if (i % 2 == 0) {
                            sheet2Cell4.setCellStyle(sheet2TitleStyle3);
                        } else {
                            sheet2Cell4.setCellStyle(sheet2TitleStyle4);
                        }
                        sheet2Cell4.setCellValue(noComplete_UnFollowUpList.get(i)[j]);
                    }
                }
            }

            //没有完成，需后续跟进
            List<String[]> noComplete_needFollowUpList = serviceResponseVo.getServiceReport().getVstarServiceReport_results().getNoComplete_needFollowUp();
            XSSFRow sheet2RowIndex2 = sheet2.createRow(manageProductList.size() + noComplete_UnFollowUpList.size() + 4 + 6);
            XSSFCell noComplete_needFollowUp = sheet2RowIndex2.createCell(0);
            noComplete_needFollowUp.setCellStyle(sheet2TitleStyle7);
            noComplete_needFollowUp.setCellValue("没有完成，需后续跟进");
            for (int i = 1; i < 14; i++) {
                noComplete_needFollowUp = sheet2RowIndex2.createCell(i);
                noComplete_needFollowUp.setCellStyle(sheet2TitleStyle7);
            }
            //创建标题
            XSSFCell sheet2Cell5 = null;
            XSSFRow sheet2Row4 = sheet2.createRow(manageProductList.size() + noComplete_UnFollowUpList.size() + 4 + 7);
            for (int i = 0; i < sheet2Title.length; i++) {
                sheet2Cell5 = sheet2Row4.createCell(i);
                sheet2Cell5.setCellValue(sheet2Title[i]);
                sheet2Cell5.setCellStyle(sheet2TitleStyle1);
            }
            XSSFCell sheet2Cell6 = null;
            XSSFRow sheet2Row5 = null;
            for (int i = 0; i < noComplete_needFollowUpList.size(); i++) {
                sheet2Row5 = sheet2.createRow(i + manageProductList.size() + noComplete_UnFollowUpList.size() + 12);
                for (int j = 0; j < noComplete_needFollowUpList.get(i).length; j++) {
                    sheet2Cell6 = sheet2Row5.createCell(j);
                    //将内容按顺序赋给对应的列对象
                    if (j == noComplete_needFollowUpList.get(i).length - 1) {
                        if (i % 2 == 0) {
                            sheet2Cell6.setCellStyle(sheet2TitleStyle2);
                        } else {
                            sheet2Cell6.setCellStyle(sheet2TitleStyle5);
                        }
                        sheet2Cell6.setCellValue(noComplete_needFollowUpList.get(i)[j]);
                    } else {
                        if (i % 2 == 0) {
                            sheet2Cell6.setCellStyle(sheet2TitleStyle3);
                        } else {
                            sheet2Cell6.setCellStyle(sheet2TitleStyle4);
                        }
                        sheet2Cell6.setCellValue(noComplete_needFollowUpList.get(i)[j]);
                    }
                }
            }

            //已完成
            List<String[]> completeReportList = serviceResponseVo.getServiceReport().getVstarServiceReport_results().getCompleteReport();
            XSSFRow sheet2RowIndex3 = sheet2.createRow(manageProductList.size() + noComplete_UnFollowUpList.size() + noComplete_needFollowUpList.size() + 4 + 11);
            XSSFCell complete_report = sheet2RowIndex3.createCell(0);
            complete_report.setCellStyle(sheet2TitleStyle7);
            complete_report.setCellValue("已完成");
            for (int i = 1; i < 14; i++) {
                complete_report = sheet2RowIndex3.createCell(i);
                complete_report.setCellStyle(sheet2TitleStyle7);
            }
            //创建标题
            XSSFCell sheet2Cell7 = null;
            XSSFRow sheet2Row6 = sheet2.createRow(manageProductList.size() + noComplete_UnFollowUpList.size() + noComplete_needFollowUpList.size() + 4 + 12);
            for (int i = 0; i < sheet2Title.length; i++) {
                sheet2Cell7 = sheet2Row6.createCell(i);
                sheet2Cell7.setCellValue(sheet2Title[i]);
                sheet2Cell7.setCellStyle(sheet2TitleStyle1);
            }
            XSSFCell sheet2Cell8 = null;
            XSSFRow sheet2Row7 = null;
            for (int i = 0; i < completeReportList.size(); i++) {
                sheet2Row7 = sheet2.createRow(i + manageProductList.size() + noComplete_UnFollowUpList.size() + noComplete_needFollowUpList.size() + 17);
                for (int j = 0; j < completeReportList.get(i).length; j++) {
                    sheet2Cell8 = sheet2Row7.createCell(j);
                    //将内容按顺序赋给对应的列对象
                    if (j == completeReportList.get(i).length - 1) {
                        if (i % 2 == 0) {
                            sheet2Cell8.setCellStyle(sheet2TitleStyle2);
                        } else {
                            sheet2Cell8.setCellStyle(sheet2TitleStyle5);
                        }
                        sheet2Cell8.setCellValue(completeReportList.get(i)[j]);
                    } else {
                        if (i % 2 == 0) {
                            sheet2Cell8.setCellStyle(sheet2TitleStyle3);
                        } else {
                            sheet2Cell8.setCellStyle(sheet2TitleStyle4);
                        }
                        sheet2Cell8.setCellValue(completeReportList.get(i)[j]);
                    }
                }
            }

            //已完成，但需其他部门跟进
            List<String[]> complete_needFollowUpList = serviceResponseVo.getServiceReport().getVstarServiceReport_results().getComplete_needFollowUp();
            XSSFRow sheet2RowIndex4 = sheet2.createRow(manageProductList.size() + noComplete_UnFollowUpList.size() + noComplete_needFollowUpList.size()
                    + completeReportList.size() + 4 + 16);
            XSSFCell complete_needFollowUp = sheet2RowIndex4.createCell(0);
            complete_needFollowUp.setCellStyle(sheet2TitleStyle7);
            complete_needFollowUp.setCellValue("已完成，但需其他部门跟进");
            for (int i = 1; i < 14; i++) {
                complete_needFollowUp = sheet2RowIndex4.createCell(i);
                complete_needFollowUp.setCellStyle(sheet2TitleStyle7);
            }
            //创建标题
            XSSFCell sheet2Cell9 = null;
            XSSFRow sheet2Row8 = sheet2.createRow(manageProductList.size() + noComplete_UnFollowUpList.size() + noComplete_needFollowUpList.size()
                    + completeReportList.size() + 4 + 17);
            for (int i = 0; i < sheet2Title.length; i++) {
                sheet2Cell9 = sheet2Row8.createCell(i);
                sheet2Cell9.setCellValue(sheet2Title[i]);
                sheet2Cell9.setCellStyle(sheet2TitleStyle1);
            }
            XSSFCell sheet2Cell10 = null;
            XSSFRow sheet2Row9 = null;
            for (int i = 0; i < complete_needFollowUpList.size(); i++) {
                sheet2Row9 = sheet2.createRow(i + manageProductList.size() + noComplete_UnFollowUpList.size() + noComplete_needFollowUpList.size()
                        + completeReportList.size() + 22);
                for (int j = 0; j < complete_needFollowUpList.get(i).length; j++) {
                    sheet2Cell10 = sheet2Row9.createCell(j);
                    //将内容按顺序赋给对应的列对象
                    if (j == complete_needFollowUpList.get(i).length - 1) {
                        if (i % 2 == 0) {
                            sheet2Cell10.setCellStyle(sheet2TitleStyle2);
                        } else {
                            sheet2Cell10.setCellStyle(sheet2TitleStyle5);
                        }
                        sheet2Cell10.setCellValue(complete_needFollowUpList.get(i)[j]);
                    } else {
                        if (i % 2 == 0) {
                            sheet2Cell10.setCellStyle(sheet2TitleStyle3);
                        } else {
                            sheet2Cell10.setCellStyle(sheet2TitleStyle4);
                        }
                        sheet2Cell10.setCellValue(complete_needFollowUpList.get(i)[j]);
                    }
                }
            }

            //计划进行该项工作，尚未开展
            List<String[]> planReportList = serviceResponseVo.getServiceReport().getVstarServiceReport_results().getPlanReport();
            XSSFRow sheet2RowIndex5 = sheet2.createRow(manageProductList.size() + noComplete_UnFollowUpList.size() + noComplete_needFollowUpList.size()
                    + completeReportList.size() + complete_needFollowUpList.size() + 4 + 21);
            XSSFCell plan_report = sheet2RowIndex5.createCell(0);
            plan_report.setCellStyle(sheet2TitleStyle7);
            plan_report.setCellValue("计划进行该项工作，尚未开展");
            for (int i = 1; i < 14; i++) {
                plan_report = sheet2RowIndex5.createCell(i);
                plan_report.setCellStyle(sheet2TitleStyle7);
            }
            //创建标题
            XSSFCell sheet2Cell11 = null;
            XSSFRow sheet2Row10 = sheet2.createRow(manageProductList.size() + noComplete_UnFollowUpList.size() + noComplete_needFollowUpList.size()
                    + completeReportList.size() + complete_needFollowUpList.size() + 4 + 22);
            for (int i = 0; i < sheet2Title.length; i++) {
                sheet2Cell11 = sheet2Row10.createCell(i);
                sheet2Cell11.setCellValue(sheet2Title[i]);
                sheet2Cell11.setCellStyle(sheet2TitleStyle1);
            }
            XSSFCell sheet2Cell12 = null;
            XSSFRow sheet2Row11 = null;
            for (int i = 0; i < planReportList.size(); i++) {
                sheet2Row11 = sheet2.createRow(i + manageProductList.size() + noComplete_UnFollowUpList.size() + noComplete_needFollowUpList.size()
                        + completeReportList.size() + complete_needFollowUpList.size() + 27);
                for (int j = 0; j < planReportList.get(i).length; j++) {
                    sheet2Cell12 = sheet2Row11.createCell(j);
                    //将内容按顺序赋给对应的列对象
                    if (j == planReportList.get(i).length - 1) {
                        if (i % 2 == 0) {
                            sheet2Cell12.setCellStyle(sheet2TitleStyle2);
                        } else {
                            sheet2Cell12.setCellStyle(sheet2TitleStyle5);
                        }
                        sheet2Cell12.setCellValue(planReportList.get(i)[j]);
                    } else {
                        if (i % 2 == 0) {
                            sheet2Cell12.setCellStyle(sheet2TitleStyle3);
                        } else {
                            sheet2Cell12.setCellStyle(sheet2TitleStyle4);
                        }
                        sheet2Cell12.setCellValue(planReportList.get(i)[j]);
                    }
                }
            }


            logger.info("生成完毕");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return workbook;
    }

    private String doubleGeTowDecimal(Double doubleNum) {
        DecimalFormat format = new DecimalFormat("0.00000");
        String doubleStr = format.format(doubleNum);
        try {
            return new String(doubleStr.getBytes("UTF-8"), "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
            return "0.00000";
        }
    }

    void setTitleStyle(String[] sheetTitle, XSSFRow row, CellStyle titleStyle, int index) {
        for (int i = index; i < sheetTitle.length; i++) {
            XSSFCell sheet1Cell = row.createCell(i);
            sheet1Cell.setCellValue(sheetTitle[i]);
            sheet1Cell.setCellStyle(titleStyle);
        }
    }

    public XSSFCellStyle getColorStyle(java.awt.Color color, XSSFWorkbook workbook) {
        XSSFCellStyle titleStyle = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        XSSFColor myColor = new XSSFColor(color);
        titleStyle.setFillForegroundColor(myColor);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle.setFont(font);
        titleStyle.setBorderLeft(BorderStyle.THIN);
        titleStyle.setLeftBorderColor(myColor);
        titleStyle.setBorderRight(BorderStyle.THIN);
        titleStyle.setRightBorderColor(myColor);
        titleStyle.setBorderTop(BorderStyle.THIN);
        titleStyle.setTopBorderColor(myColor);
        return titleStyle;
    }


}
